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.

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:

$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

Magento SEO

For those that are subscribed to the Magento RSS news feed, this may not be news to you, but for those that aren’t here is a good listing of SEO best practices for Magento. My only suggestion to Yoast was to add in Google Site map generation to their list which was added, along with a bit more about how to setup the Google site map’s thats built-in to Magento automagically generate daily or such with a cron job.

Here is the full list and I’d highly recommend alot of the tips and tricks mentioned.

arrow Magento SEO – Yoast – Tweaking Websites.

A couple other sites to perhaps check out: