TalkPHP - View Single Post

delayedinsanity · 06-02-2008, 08:27 PM

How do I do comparison on a timestamp field in MySQL? Or is it possible to use columns as data for UNIXTIME() or TIMESTAMPDIFF()? I could fix this problem fairly easily by changing the data in my MySQL timestamp column to a unix timestamp from the get go, but now that I've run into the problem I want to figure out how to fix it.

Here's an example of what I mean:

PHP Code:


			
$szGuestTimeout = time()-60*GUEST_TIMEOUT;
$szUserTimeout  = time()-60*USER_TIMEOUT;

$q1 = "DELETE FROM `".TBL_ACTIVE_GUESTS."` WHERE `last_active` < {$szGuestTimeout}";

$q2 = "DELETE FROM `".TBL_ACTIVE_USERS."` WHERE `last_active` < {$szUserTimeout}";

The column 'last_active' is a timestamp column so it stores dates in the 0000-00-00 00:00:00 format, which I'm sure you know. So I tried doing this to the variables to see if a direct comparison would work:

PHP Code:


			
$szGuestTimeout = date('Y-m-d H:i:s', time()-60*GUEST_TIMEOUT);
$szUserTimeout  = date('Y-m-d H:i:s', time()-60*USER_TIMEOUT);

But apparently it doesn't work that way. So then I thought maybe I could turn the column data into a unix timestamp with UNIX_TIMESTAMP but it doesn't seem to work if you add a column name as the data... so what should I do to perform a comparison short of switching the field to an INT and just using a regular unix timestamp as its data?
-m

06-02-2008, 08:27 PM	#1 (permalink)
delayedinsanity The Gregarious Join Date: Mar 2008 Location: Cana'derr Posts: 653 Thanks: 24	Comparing a timestamp field How do I do comparison on a timestamp field in MySQL? Or is it possible to use columns as data for UNIXTIME() or TIMESTAMPDIFF()? I could fix this problem fairly easily by changing the data in my MySQL timestamp column to a unix timestamp from the get go, but now that I've run into the problem I want to figure out how to fix it. Here's an example of what I mean: PHP Code: $szGuestTimeout = time()-60GUEST_TIMEOUT; $szUserTimeout = time()-60USER_TIMEOUT; $q1 = "DELETE FROM `".TBL_ACTIVE_GUESTS."` WHERE `last_active` < {$szGuestTimeout}"; $q2 = "DELETE FROM `".TBL_ACTIVE_USERS."` WHERE `last_active` < {$szUserTimeout}"; The column 'last_active' is a timestamp column so it stores dates in the 0000-00-00 00:00:00 format, which I'm sure you know. So I tried doing this to the variables to see if a direct comparison would work: PHP Code: `$szGuestTimeout = date('Y-m-d H:i:s', time()-60GUEST_TIMEOUT); $szUserTimeout = date('Y-m-d H:i:s', time()-60USER_TIMEOUT);` But apparently it doesn't work that way. So then I thought maybe I could turn the column data into a unix timestamp with UNIX_TIMESTAMP but it doesn't seem to work if you add a column name as the data... so what should I do to perform a comparison short of switching the field to an INT and just using a regular unix timestamp as its data? -m