[RESOLVED] Dates and Times

Kudose

'ello all.

I admit it ... I am not so hot with database functions.

Could someone teach me how to get 15 minutes before NOW()?

i.e. SELECT userID, page, ip, happened FROM tracker WHERE happened > NOW() - 1500 ORDER BY id DESC LIMIT 0,100

I think I need something like this:
SELECT userID, page, ip, happened FROM tracker WHERE happened > DATE_SUB(CONCAT(CURDATE(), CURTIME()), INTERVAL 15 MINUTE) ORDER BY id DESC LIMIT 0,100

I am not sure if the above works or not ... I don't get any errors, but nothing changes. Are both queries correct?

TIA!

suntra

Is happened a Unix timestamp OR a date format like YYYY-MM-DD ?

Kudose

It is a MySQL timestamp from NOW()

i.e. 20070112034732

bradgrafelman

EDIT: Woops, forgot that MySQL will convert the datetime to a number depending upon context... my mistake :o ... continue on with the rest of this post!

To answer your question, try using 'INTERVAL' like this:

mysql> SELECT NOW(), NOW() - INTERVAL 15 MINUTE;
2007-01-14 18:21:29     2007-01-14 18:06:29

Alternatively, take a peek at the MySQL manual: 12.5 Date and Time Functions.

Kudose

Thanks brad. I am going to use this: ... WHERE happened > NOW() - INTERVAL 15 MINUTE ORDER BY ...

bradgrafelman

Glad I could help.

Just don't do like I did and try to use "MINUTES" instead of "MINUTE" ... heh... baffled me for a few seconds :p

Kudose

Sorry to bring up old topics 😉

Why would: mysql_num_rows ... SELECT id FROM tracker GROUP BY ip result in 24

but: mysql_result ... SELECT COUNT(id) FROM tracker GROUP BY ip result in 10