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:0开发者_如何学运维0
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?
Try:
SELECT DATE(`yourtimestamp`) FROM thetablename WHERE DATE(`yourtimestam`) < CURDATE() - INTERVAL 2 DAY;
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.
精彩评论