Mysql Date Arithmetic

Manipulating dates and times in any programming language can be quite tricky. Recently I’ve been wrestling with them in both Python and MySQL.

I’ll leave Python out of it for now as that was quite a fiddle and I need to think how best to blog about it.

For the simple task I wanted to do in MySQL however I was able to get it working quite quickly.

The scenario is that I have a database that logs files transfers on an FTP server. Each transfer is logged with a date and time. There are about 4000 new records added each day and I only want to keep the last 30 days worth of records.

So I want a job I can run each day via cron that deletes records older than 30 days.

In pseudo-code this would be

DELETE FROM xfers WHERE transfer_date < today – 30

So how can I get MySQL to work out when today is and then subtract 30 days from that.

Well the magic is done with date intervals. The details of various date and time functions are shown on the MySQL developer doco.

The resultant code comes out as:-

DELETE FROM `xfers`

WHERE `transfer_date` < current_date – INTERVAL 30 day

Note that current_date is not in `backquotes` as it is a “builtin function” not a field/table name.

You can use various values with dates and times for different intervals.

Whether the way I have written the code is more or less efficient than the equivalent code

DELETE FROM `xfers`

WHERE `transfer_date` + INTERVAL 30 day  < current_date

I wouldn’t like to say and it’s not a big performance hit doing it “my way”. I think the “current_date – INTERVAL 30 day” makes more sense and is easier to understand when I look at this code in a few months time.

Advertisements

2 thoughts on “Mysql Date Arithmetic

  1. It is good practice to try and have fields “alone” on one side of an logical operation such as <. If you have an index on the field you are comparing (which you should have) mySQL will only be able to use that index if the field is alone. In other words the first example you posted is the best one (as well as being the most readable one).

  2. Ah interesting point Robert. I wasn’t aware of that effect on indexes. As I only write small quick little sysadmin type database apps I’ve not had to worry too much about performance but I should try to get into good habits. I must admit to favouring readability in code over code performance but in this instance there is a happy coincidence of the two.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s