Simon Willison’s Weblog

Storing Dates in MySQL

11th July 2003

DevShed have a new article on Date Arithmetic With MySQL, which acts as a kind of missing manual for MySQL’s powerful date arithmetic functions. It reminded me of something I’ve been meaning to write about for some time: my thoughts on storing dates in a PHP application that uses a MySQL backend

MySQL comes with a full featured API for handling dates, and several column types for use with the date and time functions. Unfortuantely, none of the available column types map directly to PHP’s internal format for handling dates, the Unix timestamp (an integer value recording the seconds since midnight on January 1st, 1970). I have seen many PHP applications where people have used a MySQL integer field to store date information as a Unix timestamp. While this works, it is inadvisable as it prevents you from using any of MySQLs powerful built in date handling abilities.

Instead, you can use MySQL’s UNIX_TIMESTAMP() and FROM_UNIXTIME() functions to convert Unix timestamps to MySQL date types as part of your SQL queries. This allows you to use MySQL’s internal date manipulation features without having to manually convert MySQL dates to PHP timestamps in your PHP applications. I usually use DATETIME fields to store timestamps, but the conversion functions work for any of MySQL’s date storage types.

Here’s a sample select query:

SELECT 
  entries.*, UNIX_TIMESTAMP(added) as unixtime
FROM 
  enries
...

Assuming added is a DATETIME column, this adds an additional field to each returned row called ’unixtime’, containing an integer that can be passed straight to PHP’s handy date() function for formatting. Going the other way:

INSERT INTO 
  entries
SET  
  title = 'The title', 
  added = FROM_UNIXTIME(1057941242),
...

The second example is less useful, but at least demonstrates the function. Incidentally, when inserting things in to a MySQL database with the current time it’s generally a good idea to use MySQL’s NOW() function to set the time, like this:

INSERT INTO 
  entries
SET  
  title = 'The title', 
  added = NOW(),
...

Doing this increases consistency as it means that should you ever have a setup with multiple web servers talking to a single database server the database server’s time will be used as the standard, rather than potentially introducing errors from differing clocks on the server machines.