CODEWITHSUNDEEP
mysql
Lothar
Lothar

Reputation: 3489

Comparing timestamps in MySQL

The timestamp in my database is in the following format:

2011-02-26T13:00:00-05:00

But getting the current timestamp via MySQL with:

SELECT current_timestamp();

Gives me something formatted like...

2011-02-26 13:05:00

My end goal is to go through all entries (each 2 days) and delete those older than 2 days so how would I compare the entries with the 2011-02-26T13:00:00-05:00 timestamp to the current timestamp?

Upvotes: 7

Views: 20353

Answers (2)

rg88
rg88

Reputation: 20977

Try:

SELECT DATE(`yourtimestamp`) FROM thetablename WHERE DATE(`yourtimestam`) < CURDATE() - INTERVAL 2 DAY;

Upvotes: 14

andri
andri

Reputation: 11292

Sounds like you store your date (in your preferred format) in a character-based column (VARCHAR et al).

Don't. It's a) bad style, b) you run into issues such as this. Instead, use the standard timestamp type, and if you need that specific ISO format when querying from the database, then format it accordingly.

If you store your times in the native format, then comparisons between different dates become trivial < and > comparisons.

Upvotes: 0

Related Questions