Having dealt with a lot of dates and times — and the calculations between them — recently, I thought it might be a good idea to write down some of the issues I’ve encountered.
The truth is, dates and times are hard! There are many different rules that make dealing with dates and times non-trivial. Often, people make mistakes, causing loss of information and general confusion.
Deciding on a format
One of the observations I’ve made is that nearly every system out there lacks a standard for passing around date and time values.
The most common formats are MySQL (YYYY-MM-DD HH:MM:SS), timestamps (what time() produces), and DateTime.
There are benefits to all of these, but it’s important that you need to decide on one.
Make sure any date/time that enters your
app through any means is converted immediately to this standard.
This includes, for example, the result of a MySQL query, data from $_POST,
or the response from a web service.
Make sure this enters your coding standards document.
Using DateTime
Deciding on a standard date format and following it religiously is much more important than which standard you choose. If you have a choice, though, I recommend the DateTime object.
The standard inclusion of the DateTime object in PHP is one of the best things that has happened to PHP in recent times. It replaces all the functionality of the existing date functions, and there’s really no need to use anything else.
One of the benefits you’ll get is that you can type hint it like every other class, which means it will be easier to spot bugs, and it’s self-documenting. Also, you can pretty much treat it like a timestamp:
$dt1 = new DateTime('yesterday');
$dt2 = new DateTime('next wednesday');
if ($dt1 > $dt2) {
echo "Hell froze over!\n"
};
As you can see, because DateTime is actually a PHP extension, it gets the privilege of overloading operators such as < and >. Plus, it handles timezones.
DateTime also makes it easy to do relative calculations. If you want to increase a date by exactly one month, how many seconds you need to add the start date depends on the month of the year, wether it’s a leap year or not, which timezone you’re in, and whether a leap second has been scheduled that year. It’s easier let DateTime do it:
$dt = new DateTime('December 25th, 2011');
$dt->modify('+1 month');
Using time zones
I’ve noticed that a lot of people simply use their local timezone. This is a bad idea, because it’s not always immediately clear, it’s ambiguous, and it can cause obscure bugs. It will also not always be possible to convert to different timezones if your app ends up going international.
The reason it’s ambiguous is simple. Every year, in most European countries, the end of Daylight savings time is the last Sunday in October. On that day, every second between 02:00 and 03:00 occurs twice. Thus, one ambiguity when using time zones is that these seconds that occur twice are not unique, so you can no longer determine if a time is referring to before the switchover or after. This can cause bugs when doing date comparisons. Adding a second could mean the same thing as subtracting 59 minutes and 59 seconds.
The start of DST is similar, which is why you might see gaps in log files and whatnot.
So, make sure that when you deploy your app, the server as well as PHP are configured to use UTC or GMT. UTC time is time zone independent, so every second of the year is, well, unique.
You’ll have to get into the habit of converting times to your local time zone in your presentation layer. On the plus side, it will be easy to support other time zones in the future.
You can always accurately convert from UTC to your local time zone, but you cannot always accurately convert from your local time zone to UTC. (It is a lossy conversion.)
// Changing time zones
$dt = new DateTime('2011-10-07 12:00:00');
$dt->setTimeZone(new DateTimeZone('Europe/Lisbon'));
echo $dt->format(DateTime::RFC2822);
Storing dates and times in MySQL
There are three possible data types you can use to store dates and times in MySQL. You can use DATETIME, TIMESTAMP, or INT (for storing a timestamp).
DATETIME is a popular choice, and in most cases, it’s fine. Just remember that DATETIME is 8 bytes, whereas a TIMESTAMP or INT is just 4 bytes. The biggest benefit of DATETIME is that it’s easy to do calculations in queries, whereas this may is harder with a TIMESTAMP and INT.
I personally dislike TIMESTAMP, because it does automatic time zone conversion. This is fine if you keep everything set to UTC, but if this ever changes, it can cause unexpected results.
I tend to store dates and times as timestamps (using INT). The biggest drawback is that you’re missing some of the date handling features MySQL would otherwise provide, and it’s not clear what date and time you’re looking at when checking out the raw data in a MySQL console or other app. The benefit is that it’s never ambiguous. Timestamps are always based on UTC, so the chance of making mistakes is lower.
Storing time zones
There are a bunch of cases where you might want to store time zones. One example is that your users may have their own preferred time zones.
If you’re in need of this, never store the offset (e.g., -0500). Instead, store the local time zone name, such as Europe/Amsterdam, Canada/Eastern, &c.
The offset alone is ambiguous. For countries that observe DST, the offset changes twice each year, and not necessarily on the same day. Many countries don’t observe it at all.
The US and Canada recently (in 2007) shifted the day DST starts by a few weeks. In Australia, some states observe it, and other states don’t. Newfoundland in Canada has experimented with a 2-hour shift, and the time changes at 00:01. In Brazil, it’s completely crazy; they tend to change the rules quite frequently. (The cost of everyone having to deal with this is huge.)
By storing Europe/Amsterdam, if the rules change again (and they do almost every year, somewhere in the world), you will be prepared.
Summary
- Dates and times are difficult to work with, but very interesting!
- Use PHP’s DateTime.
- Always store dates and times in UTC.
- Make sure your operating system, PHP, and MySQL are all set to UTC.
- If you need to store time zones, use the region, but store the actual date and time in UTC.
I hope this was helpful. Many programmers hate having to deal with dates and times and time zones, but if you stick to these simple rules, it will be much easier.
Developer Gift
I’m embarrassed to say that, until recently, I hadn’t read a good book in several years. That all changed when I read what is hands-down my favorite book this year, Hyperion by Dan Simmons.
Among sci-fi geeks, it’s considered a classic. It’s a relatively easy read, and it’s not very expensive. If you do read it (or have read it), let me know what you thought!
