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.
More recent articles
- OpenAI DevDay: Let’s build developer tools, not digital God - 2nd October 2024
- OpenAI DevDay 2024 live blog - 1st October 2024
- Weeknotes: Three podcasts, two trips and a new plugin system - 30th September 2024