Oct 8 2009

Selecting 30 days or older entries using MySQL DateTime field

I’ve been doing quite a bit of Date and Time manipulations lately, and have grown to enjoy MySQL’s DateTime fields just as much as UNIX timestamps. Particularly its built-in functions for selecting any range of fields based on a time frame.

Needing to remove all entries from a table that are older than 30 days, at first I thought I would need to iterate over the very large table (90+k) rows of data and convert each MySQL DateTime field to a UNIX time stamp to do the calculations myself with PHP. However after a bit of research I’ve learned of a few things with MySQL DateTime that make this a lot easier.

?View Code SQL
1

SELECT fields FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= timestamp;

The “timestamp” field is the MySQL DateTime field. Using the DATE_SUB, CURDATE and INTERVAL one can easily grab all records that are 30 days old without the headache of converting to UNIX timestamps in PHP and doing the grunt work yourself.

:idea: Also as a side note, if you wish to convert a MySQL DateTime field to a UNIX timestamp you can easily do this by:

?View Code PHP
1

$mysql_timestamp = gmdate("Y-m-d H:i:s",$mysql_timestamp_value);

If anyone has any better more efficient means to accomplish this please feel free to leave a comment. :D

Possibly Related Posts:


no comments   |  tags: , , , | posted in MySQL, Web Development