[RESOLVED] Selecting table elements based on ranges (mysql)

[PureDrive]

Hi, I have a timestamp column in one of my tables and I need to select some elements from the table base on the ranges some timestamps fall in. For example, I need to select all the elements from one date to the next. Are there any mysql keywords that deal with ranges? Thanks!

[bpat1434]

Timestamps, like Unix Timestamps, or MySQL Date/Time stamps? Is it in the form of:

20071225 (YYYYMMDD)

or

1248581086172 (seconds)

If you're using a typical MySQL Date/Time field (not the Unix timestamp) field where you can look at it in the DB and know the date (first example) then you can use functions like:
DATE_SUB, DATE_ADD, DATE_DIFF and such to deal with ranges. You can even just do the regular comparison:

SELECT * FROM table WHERE date_time >= '20071225'

If you're using a unix timestamp, you can do the regular comparison (see above) or, you can use the FROM_UNIXTIME function to turn it into a MySQL date and use the DATE_ADD and DATE_SUB functions.

Depending upon what you're trying to do exactly, something like this would be perfectly okay:

SELECT * FROM table WHERE date <= NOW() AND date >= DATE_SUB(NOW(), INTERVAL 24 HOUR)

Hope that helps. HERE is the MySQL documentation entry for all their Date and Time functions 😉 Very very helpful.

[PureDrive]

I am using the UTC_TIMESTAMP() mysql function to enter my timestamps.

[NogDog]

. . . WHERE timestamp_column BETWEEN '2007-12-10' AND '2008-01-01'