Comparing a timestamp field

delayedinsanity · 06-02-2008, 07: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

Wildhoney · 06-02-2008, 09:42 PM

It'd be something like the following. Simply convert the date in the database to a unix timestamp on-the-fly, and compare it against the current timestamp.

sql Code:

DELETE FROM
    myTable
WHERE
    UNIX_TIMESTAMP(myLastActiveColumn) < UNIX_TIMESTAMP()

delayedinsanity · 06-02-2008, 09:49 PM

AHHHHH. Once again I was right on top of the answer, and missed it by an inch... see, I tried using UNIX_TIMESTAMP() on the column and it didn't work. I wasn't sure if you could feed it a column as a value or if you had to enter a hard coded value of some sort, but it turns out that the reason it didn't work is because I had already tried converting the timeout variables with the date() function and never changed them back. So in essence, I switched it from trying to validate 0000-00-00 00:00:00 against 2133421342 to trying to validate 2133421342 against 0000-00-00 00:00:00 . *rolls eyes*

It never ends... never.
-m