MySQL date functions

Here is a little collection of my favourite MySQL functions for manipulating dates.

DATE_FORMAT

Ever since I discovered the DATE_FORMAT function in MySQL I have been addicted. It started with it’s intended use, which is something like convert this timestamp to this human readable format. It just rocks, I’m not writing a real query unless I’m using DATE_FORMAT. So for example

DATE_FORMAT(NOW(),’%Y-%-m-%d’)

or when I’m grouping by month it looks like

DATE_FORMAT(NOW(),’%Y-%-m’)

Another cool thing is

DATE_FORMAT(NOW(),’%Y-%m-01’)

when you want the first day of any month. It just simply is so useful.

DATE_ADD and DATE_SUB

Then comes DATE_ADD and DATE_SUB. These get used all of the time. I want to build a query that looks at data of the year that has passed. Simple in my WHERE clause I add some

fieldname BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()

That is a simple query because the results will vary if new data is added. So there has been the case where I have needed to get me a year’s worth of data starting from the beginning of the current month. Simple combining NOW,DATE_FORMAT and DATE_SUB. So that looks something like

fieldname BETWEEN DATE_SUB(DATE_FORMAT(NOW(),’%Y-%m-01'), INTERVAL 1 YEAR) AND DATE_FORMAT(NOW(),’%Y-%m-01')

LAST_DAY

When you need to work out the last day of a month, this is your saviour. Nice and simple to use.

PERIOD_DIFF

This one is useful for finding the number of months between two dates. I often use this one to find how long someone has been a member at work.