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

MysqlDatetimeUtc

Mysql Problem Overview

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.

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

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

SELECT * FROM table WHERE DATEDIFF(NOW(),colname) > 7;

Solution 3 - Mysql

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

OR

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

Result:

2007-12-26

Categories
Recommended Mysql Solutions
Recommended Datetime Solutions
Recommended Utc Solutions

Previous Solution:

How to call a stored procedure from Java and JPA

JavaSqlStored ProceduresJpaJboss

Next Solution:

Is it possible to use Razor View Engine outside asp.net

asp.netasp.net MvcCode GenerationTemplatingRazor

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questiontzmatt7447View Question on Stackoverflow
Solution 1 - Mysqlreko_tView Answer on Stackoverflow
Solution 2 - MysqlSajjad ShirazyView Answer on Stackoverflow
Solution 3 - MysqlMichael PakhantsovView Answer on Stackoverflow