MYSQL query / dates older than 1 week ago (all datetimes in UTC)

Question

Hi,

How do I query a mysql db to return all records with a datetime older than 1 week ago. Note that the datetime table stores everything in UTC, and i should be comparing it in that itself...

Jusst to be clear - I'm looking for a pure mysql query

Answer 1

SELECT SUBDATE('2008-01-02', 7);

OR

SELECT SUBDATE(now(), INTERVAL 1 week);

Result:

2007-12-26

Answer 2

SELECT * FROM tbl WHERE datetime < NOW() - INTERVAL 1 WEEK

If your table stores datetimes in different timezone than what NOW() returns, you can use UTC_TIMESTAMP() instead to get the timestamp in UTC.