When dealing with dates and times you can easily find a handful of formats representing the exact same time. For example
- Timestamp: 1349697903
- German human: 8.10.12 14:05:03
- MySQL: 2012-10-08 14:05:03
- E-Mail: Mon, 08 Oct 2012 14:05:03 +0200
- ISO 8601: 2012-10-08T14:05:03+02:00
The granularity of all these five formats is 1 second, which is fine for online actions. For our daily workflow it would be enough to see the minute of a message in our inbox, but sometimes we need to know the order of a bunch of messages that might arrive within a single minute.
The date format for Kullo should be human readable. Developers have to love it for testing and debugging because they create our software! So the timestamp is not an option. A timestamp might perform well when doing a lot of calculations time differences and time ranges since it is an integer. But we don’t need that.
Local formats do not make a lot of sense as well. They will come into play for client software later.
The information about the time zone is helpful when sending messages around the globe. They allow to refer to your local time without calculating into UTC, which would reduce the readability. But a reply message sent at nine o’clock from New York City will be sorted behind the original message sent at ten o’clock from Berlin.
We are not interested in the day of the week, since that is a redundant information. Client software will look it up if necessary.
Another aspect is sortability. When sorting German dates, you have to split and convert the date string to a vector ((int) year, (int) month, (int) day, (int) hour, (int) minute, (int) second), sort it lexicographically, and convert it back again. With MySQL and ISO 8601 you can do simple lexicographical string sorting because we have a correct order and always have leading zeros.
Thus ISO 8601 is in my opinion the best overall format for international date and time fields. It’s strict structure make it easy to convert. There is no white space, nothing optional et cetera. Since ISO 8601 defines a bunch of different formats for different purposes, we only use the profile specified the NOTE-datetime by the W3C:
YYYY = four-digit year
MM = two-digit month (01=January, etc.)
DD = two-digit day of month (01 through 31)
hh = two digits of hour (00 through 23) (am/pm NOT allowed)
mm = two digits of minute (00 through 59)
ss = two digits of second (00 through 59)
TZD = time zone designator (Z or +hh:mm or -hh:mm)