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.