Dates functions with MySQL and unix timestamp

MySQL has powerful functions built in to manipulate data with dates. These functions only work within MySQL providing your using their own date/time stamp. If your like me, your much rather use the PHP function time() to get the Unix timestamp and store that in the database. It seems a shame to miss out on these features because we use Unix time. Never fear, the following examples shows how to get round this using FROM_UNIXTIME(). So what happens now if we want to SELECT or GROUP data by periods, such as by week, month or year. Take one look in the MySQL manual and there are few practical examples on how this can be done using only the Unix timestamp. Below I run through a practical example and how this can easily be achieved. If you want a mock up table to try it out, run the following SQL. This table is a vastly simplified version of a e-commerce system order table, but the logic here could be applied to any type of database driven web site.

CREATE TABLE `orders` (
`order_ID` int(11) NOT NULL auto_increment,
`timeordered` int(11) NOT NULL default '0',
`grandtotal` double(10,2) NOT NULL default '0.00'
PRIMARY KEY  (`order_ID`)) ;

This table contains the Unix timestamp in `timeordered` and `grandtotal` contains a decimal number. We are going to construct a SQL query that will list the number of orders in a month, the total amount of all those orders and the average value of those orders. Lines 1, 2, 4 are standard functions used to calculate our values. The interesting lines are 5 and 6. If I was using the normal MySQL timstamp and could simply write GROUP BY MONTH(), YEAR() to achieve the results we want. You may ask why are we using both MONTH() and YEAR() when we just need it to group by MONTH(). Well the answer is you could just use MONTH(), but that would only ever return 12 rows (1 for each month) no matter over how many years. So for example it would group January 2004, January 2005 or any other January. For some systems this would be undesirable. Using YEAR() ensure only one month and one year is in one return row.

SELECT COUNT(order_ID) as `number`,
SUM(grandtotal) as `cgrandtotal`,
FROM_UNIXTIME(timeordered, '%M %Y') as `fdate`,
AVG(grandtotal) AS `averagegrand`
FROM `orders`
GROUP BY MONTH(FROM_UNIXTIME(timeordered,'%Y-%m-%d %H.%i.%s')),
YEAR(FROM_UNIXTIME(timeordered,'%Y-%m-%d %H.%i.%s'))
ORDER BY timeordered DESC

Normally we put the column name in the brackets of the function of the MySQL date (ie YEAR(column-name)). But because we are using Unix time we must use the functionFROM_UNIXTIME(column-name-of-unixtime, format). The format parameter simply specifies how to turn the Unix time to a understandable timestamp for MySQL, the ones shown on lines 7 and 8 seem to work fine, I believe there may be shorter ones that can be used.

So the query above will return something like this (after adding your own dummy data!):

NUMBER	CGRANDTOTAL	FDATE	AVERAGEGRAND
8	532.00	January 2008	66.50
21	1201.20	December 2007	57.20
17	1041.25	November 2007	61.25

The example below assumes you have some basic knowledge of SQL and has been tested on MySQL 4.1.