After I did a talk on Timezones, I often get asked the same Question:
why not simply convert all dateTime to UTC when saving to DB? Then display with appropriate timezone
Here’s why:
Remember your Summer-vacation 2016 in Antalya, Turkey? Oh, come on: just for the sake of an example!
You met this other person in and you made an appointment for a christmas call for the 25th of December at 14:00H local Turkish Time. So your appointment is (in ISO, because we use ISO-Datetimes, don’t we?) 2016-12-25 14:00:00 Europe/Istanbul. At the time you made the appointment you would then convert that to UTC using the TimezoneDB that is available at that moment. That would resolve to 2016-12-25 12:00:00 UTC. At the time you made the appointment it was DaylightSavingTime (so it had an offset of 3 hours at that time) but the TimezoneDB already had the information that Turkey would move back from DST sometime in October so at the time of your appointment the offset would only be 2 hours. So everything is fine.
Now the 25th of December arrives and you take your Phone at 12:00:00 UTC and dial the number. And then it happens! No one is answering your call. What the heck?
What happened? The person you wanted to call simply wasn’t at home (or wherever the phone was or had the phone turned off) because they waited for 3 quarters of an hour for your phonecall and then simply gave up and went to wherever.
Why? Well, when you made your appointment, no one knew, that the Turkish gouvernment would decide to stay on DST even in winter. They never switched back from DST so their offset to UTC is still 3 hours at the 25th of December. So your conversion to UTC was based on the assumption that Turkey would move back from DST as they did for multiple years in a row. But as politics go, they suddenly decided to not do that any more. So you made your conversion on an assumption. Had you noted 14:00 Europe/Istambul instead of 12:00 UTC everything would have been fine, as your updated timezone-DB would have easily reminded you at the correct time (11:00 UTC) of your appointment.
YOu might think, that the timezonedb doesn’t that often change: At the time of writing this the current timezone-database you can download at the IANA is version 2016j. That’s the 10th revision of the database in 2016. and every release contains more than one change. So at least 10 changes to timezone entries alone in 2016. And those changes are not only such changes as turkeys remaining in DST (which was announced about 1 month in advance) but there are such things as Tonga moving from an offset of +13:00 to -14:00 (skipping a whole day on the way) in 2008 or a simple “Hey, we just heard that Antarctic station XYZ moved their offset by 3 hours 3 weeks ago”.
The timezone-database is “valid” for about ± 4 weeks but the further a datetime is from the release date of the current timezone-database the more insecure it becomes to convert to or from a certain timezone.
Did that make sense? Feel free to comment or ping me via email, twitter or on IRC to discuss the matters.
Your right. It feels like a train ride in the 18th century. At least if your not using UTC you can’t be sure the train arrives in your timezone.
By the way, Doctrine says following about this problem:
“The solution for this dilemma is simple. Don’t use timezones with DateTime and Doctrine 2. However there is a workaround that even allows correct date-time handling with timezones:
Always convert any DateTime instance to UTC.
Only set Timezones for displaying purposes
Save the Timezone in the Entity for persistence.”
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/working-with-datetime.html#handling-different-timezones-with-the-datetime-type
And another good article about handling timezones is
https://derickrethans.nl/storing-date-time-in-database.html
Thank you for your post! It made me think about the problem again.
I hope I could point out why the “one-for-all” approach of the Doctrine-Project isn’t the real McCoy for all DateTime-related things! Though handling DateTimes with timezones in different Databases is challenging.
For PDO I wrote a small lib to handle that for SQLite, MySQL and PostgreSQL
Oh, and when you’re using Timezones you should be sure the train arrives in the right timezone. You might not be sure it has the offset you expected though 😉
For your specific use case, an appointment in the future, it might make sense. The other person is an external dependency in this case. You could also conspire with the other person to free yourselves from local politics (a dependency on the government of Erdogan) and both register the time of the appointment in UTC. I prefer this solution as the UTC gives you a clear moment set in time; it is universal and gives the least chance for confusion. Whenever possible, I always use UTC, and convert in the view layer for localized views.
Just two small remarks:
Future in terms of Timezones might mean anything after about 2 weeks from now. And there have even been backwards related changes in the timezone-Database.
A local time with a timezone gives you an equally clear moment in time. With the added advantage that no one needs to convert anything from local time to something else. And as tools like IntlDateFormatter or momentjs easily handle dates with timezones there is actually no need to convert anything…