NOW()

[jmaccs64]

I am hoping this would return all rows within the last 30 seconds...

 

timestamp is in this format

2011-08-26 07:26:23

 

SELECT id FROM idtrack WHERE NOW() > timestamp > (DATE_SUB(NOW(), INTERVAL 30 SECOND))

 

its not... Thanks-In-Advance!

[Pikachu2000]

Why are you using two consecutive greater than comparisons like that?

[cocoscrappy]

Try this:

$off = strtotime("-30 seconds");
        $time = date("Y-m-d H:i:s", $off);
        $res  = mysql_query("SELECT id FROM idtrack WHERE timestamp > '$time'");

Not tested...

[gizmola]

What mysql data type is timestamp?

[fenway]

You want:

 

SELECT id FROM idtrack WHERE timestamp >= NOW() - INTERVAL 30 SECOND

[The Little Guy]

fenway, is that the same as this?

 

SELECT id FROM idtrack WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 SECOND);

 

if so, which is faster?

[AyKay47]

same thing.. different way of writing it.. preference

[fenway]

same thing.. different way of writing it.. preference

Actually, they're not the same thing with invalid dates.

[AyKay47]

same thing.. different way of writing it.. preference

Actually, they're not the same thing with invalid dates.

are you saying that they handle invalid dates differently..?

[fenway]

That's exactly what I'm saying -- it's undocumented, and it's evil.

[AyKay47]

his method is absolutely documented..

[fenway]

his method is absolutely documented..

The difference between the functions on invalid dates isn't.

[The Little Guy]

I'm sorry, but which one then supports the invalid dates?

[gizmola]

I think fenway is just pointing out that the mysql date functions will do some strange things when dealing with invalid dates, and this isn't necessarily documented.  I've done a lot of looking at mysql date handling and what I found is that mysql comes in a mode where it will accept invalid dates in many cases.  This is actually by design, and can be reconfigured in the latest version of mysql.  For example, you can store a datetime that has zeros in it, or a valid year and invalid month/day or any variation.  You can now change the configuration of mysql so that it does not allow invalid dates, but that is not the default, nor have I personally played with the setting.

 

In this particular case, the point is moot, because the parameter NOW() will always be valid.  One other thing about DATE_SUB, is that it is fairly new function, whereas DATE_ADD has been available for a long time.  You can achieve the same effect with DATE_ADD by passing negative interval values ie. (INTERVAL -30 SECOND), and I always use DATE_ADD for that reason.

 

In conclusion: the OP has not been seen in this thread for a while.  I never got an answer on whether or not the column in question is a datetime, an integer or a mysql timestamp.  That is an important piece of information.  Regardless, he seems no longer to care about this thread, so let's let it go off to sleep.