Datetimes in PHP and MySQL

Until a few days ago, I’d never properly figured out exactly how to handle datetimes in PHP – whether to use date() or gmdate() in various situations, the best thing to do when dates are going to or coming from a database and how to handle timezones.

I just sort of winged it, mostly using date() and hoping timezones would just behave. But here’s what I’ve now found out.

Storing to and retrieving from MySQL

Datetimes are stored in MySQL very simply – the timezone doesn’t affect how they are stored or retrieved at all. And just so we’re clear, the timezone isn’t stored alongside a date either – only the familiar YYYY-MM-DD HH:MM:SS format. For this reason, it’s clearly essential to always store datetimes in a single timezone, and the best contender is UTC since this gets around daylight saving time issues and is… well, universal. (In fact, Unix timestamps aren’t true representations of UTC, but they’re damn close.)

Whether to store these UTC datetimes as Unix timestamps (number of seconds since the start of 1970, UTC) or in columns of the MySQL datetime datatype is up to you. I personally opt to use datetime fields since I often dig around with a commandline MySQL client – to understand the timestamps I’d have to put them through MySQL’s FROM_UNIXTIME() every time.

So given a Unix timestamp, perhaps generated by PHP’s time() or strtotime(), you need to give it to MySQL in the format it wants, Y-m-d H:i:s. You don’t want MySQL to store the the time in your local timezone, so use gmdate().

To get it back you’ll almost certainly want to put it through strtotime() since a timestamp is the easiest form in which to manipulate a datetime in PHP. But just sticking the date string from the database through that function will make PHP interpret it as being in the server’s current timezone. This is no good if the server is not always in UTC, so specify the timezone: strtotime($datestring . " UTC").

Timezones in MySQL

As noted above, MySQL doesn’t care about timezones when storing or retrieving datetimes. The only time MySQL cares about timezones is when certain built-in date functions are used – NOW(), for instance, returns the current datetime in the currently set timezone.

I don’t actually use MySQL’s date functions very often, preferring to let PHP do the work. Check the MySQL manual to find out about the UTC functions if you need them, but the most important are UTC_TIMESTAMP(), which returns the current datetime as a string, and UNIX_TIMESTAMP(), which returns a Unix timestamp, funnily enough.

What if you need to use NOW() to get the current local datetime, but the server isn’t using the timezone you want? You can override the timezone which will be used for that session with a query such as SET time_zone = '+01:00';. Set the timezone as soon as you connect to the database to whatever timezone PHP is in (info on setting that below) with mysql_query("SET time_zone = '" . date("P") . "';"); soon after your mysql_connect() call.

Timezones and daylight saving time in PHP

In order to show dates in suitable local time, it’s important to set the timezone. You can set the timezone which suits the site best (for instance a UK business would use UK time) or allow the user to choose their own. You could even try to detect the user’s location and apply a timezone based on that, but in my experience this is dodgy at best.

I have some UK-centric sites hosted on a server in the USA and so before I generate any output I set the timezone with date_default_timezone_set("Europe/London"); – the supported timezones are shown at php.net/timezones. Daylight saving time is worked into it – use the timezone of a city which observes the daylight saving time rules you want your site to observe and everything will be handled for you. If you want to force a particular timezone with no daylight saving, there are suitable fixed timezones under the “Others” header such as UTC and Etc/GMT+5.

One thing which I found very confusing at first is how PHP handles daylight saving time when you give it a datetime in the past or future. It turns out I needn’t have been confused – maybe it’s blindingly obvious, but it just didn’t click with me – PHP simply uses the state of daylight saving time at whatever date it is considering. For example, given a London timezone and a couple of timestamps $xmas2008 = strtotime("2008-12-25 08:00"); $birthday = strtotime("1986-07-25 noon");, if you put them through date() you get a GMT datetime for $xmas2008 and a BST datetime for $birthday. You can verify this by outputting a full-format date including an indication of timezone.

What this means is that given a correctly set timezone, the output of date() given a correct Unix timestamp will always make sense, no matter when the timestamp was stored, what date it represents nor what the current date is. As an illustration, say you publish a blog post just after midnight once a week all year. Even though the state of daylight saving time changed twice (if you’re in an area which observes DST, at least), the blog posts will always show as just after midnight. If you then change the timezone to a region at roughly the same longitude but which doesn’t observe DST (Iceland is an example of an always-GMT region), you’ll see that some of your blog posts show as just after 11pm – these are the ones you made during DST – and the rest still show as just after midnight. Use date() with a format including the timezone to see for yourself.

User-entered datetimes

The only thing left is taking datetimes from user input. The best option here is to feed the user input through strtotime() without a timezone suffix, and hence have PHP interpret the time in whatever timezone applied (or will apply) on the given date. If you allow your users to set a custom timezone, this means the datetimes will (should) be interpreted exactly as the user intends. The other option would be to demand UTC input, but not everyone would know, if the date is a few months away, whether to shift the date by an hour or not and, if so, in which direction to shift it.

To sum up

• Set suitable timezones in both PHP and MySQL with date_default_timezone_set("Europe/London"); mysql_query("SET time_zone = '" . date("P") . "';");
• Store and manipulate datetimes in PHP as Unix timestamps, converting them for display with date() only when necessary. I like to give HTML datetimes a title attribute with a full-format date such as Y-m-d H:i:s P (you can use T instead of P to show the timezone’s abbreviation rather than its offset from UTC) so things are totally clear.
• To store a datetime in MySQL, store it as a timestamp (integer) directly or store the output of gmdate("Y-m-d H:i:s", $timestamp) to a column with the datetime datatype – gmdate() rather than date() to ensure a UTC datetime is stored
• To retrieve a datetime stored in a datetime column, use strtotime($datestring . " UTC") to ensure it is interpreted as UTC
• Put datetime input from users directly into strtotime() to get a timestamp, then store or display it as above

I hope this helps someone who is as confused as I was. If you have any other datetime-related tips, go ahead and leave a comment.