[Resolved] PHP Time and SQL Time?

Sayian

Does anyone know how to make time() use the same format as the MYSQL timestamp 14 char?

Weedpacket

I understand MySQL has a UNIX_TIMESTAMP function or something with a name something like that. Look it up in the section in MySQL's manual on its date and time types.

Sayian

What im trying to do is compare the php time time() to a MYSQL timestamp , but they are totally different and they wont compare , so i need to get the php time time() in the MYSQL 14 int format to be able to do the comparision in my script

Sayian

Ok , ive read up and found this

To convert a MySQL timestamp to a Unix-style timestamp, use MySQL's UNIX_TIMESTAMP function.

For Example:
$result=mysql_query ("SELECT UNIX_TIMESTAMP(timestamp_column) as epoch_time FROM table");

$unix_timestamp = mysql_result ($result, 0, 0);

This is my php script , which selects all my active user's , grabs the timestamp from the table colom , and checks it against the current time to see if they should be taken out of the active table.

But the MYSQL and PHP timestamps are different i cant compare the current time time() to the mysql stored time , so i gotta use the function above. But im clueless how to incorporate this into my current query.

$time = $date;
$ro = mysql_query("SELECT * FROM shocc_active");
$total = mysql_num_rows($ro);

// gets the time
$time = time();

// Makes sure the user is still active
while ($ru = mysql_fetch_array($ro)) {
$act = $ru['timestamp'] + 500 * 12;
$del = $ru['name'];
if (!$del) {
mysql_query("DELETE FROM `shocc_active` WHERE `name`='$del'");
}
if (strlen($del) <= 3) {
mysql_query("DELETE FROM `shocc_active` WHERE `name`='$del'");
}
if ($act < time()) {
mysql_query("DELETE FROM `shocc_active` WHERE `name`='$del'");
}
}

Any ideas on how i could do this?

kubis

Originally posted by Sayian

$time = $date;
$ro = mysql_query("SELECT * FROM shocc_active");
$total = mysql_num_rows($ro);

// gets the time
$time = time();

// Makes sure the user is still active
while ($ru = mysql_fetch_array($ro)) {
$act = $ru['timestamp'] + 500 * 12;
$del = $ru['name'];
if (!$del) {
mysql_query("DELETE FROM `shocc_active` WHERE `name`='$del'");
}
if (strlen($del) <= 3) {
mysql_query("DELETE FROM `shocc_active` WHERE `name`='$del'");
}
if ($act < time()) {
mysql_query("DELETE FROM `shocc_active` WHERE `name`='$del'");
}
}

Any ideas on how i could do this? [/B]

a) its a very bad idea to have a column with name "timestamp"; use "ts" or something different, as "timestamp" is a reserved word (or could be in the future).

b) do the query as this:

SELECT *, UNIX_TIMESTAMP(timestampcolumn) AS timestampcolumn_ts FROM table

then you will have the resultset with:

print($rs['timestampcolumn']); //prints the SQL way
print($rs['timestampcolumn_ts']); //prints unixtime

if(time() > $rs['timestampcolumn_ts']){
  //your code here
}

Weedpacket

It's also a bad idea to use SELECT * at all, unless you really do want every single field but you don't know what they're called.