开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜