Laravel: Timezones in the database?
Damn Daylight Savings!
Managing time in the world of computers is a mess. Moreover, dealing with timezones can make-or-break an application that is not prepared from the ground up to manipulate it.
Most of the time (no pun intended) any event is set in stone, that moment is the same for everyone, but sometimes DST and timezone differences can get in the way.
For example, let’s take the UEFA Champions Final as an hypothetical example. For some reason, the kickoff is set to next Sunday at 21:00 in the Stade de France, local time. Realistically it’s Saturday, but is easier to grasp.
Everyone else will have different kickoff times for their respective places. For example, those living in Liverpool will see the match when their clocks turn 20:00, while those in Japan will have to wake up Monday at 4:00.
When an event is the same for everybody, it’s safe to persist the time as UTC into the database, and then show the time to the user by applying its local timezone with utilities like Luxon.js.
But what about the time in France itself? For the sake of an example, let’s imagine that the week prior to the final they enter Daylight Saving Time (DST), which adds to a total of +2 hours.
How everyone won’t miss the final? Does the final kickoff moment changes?
Pour moi, it will.
We would think that using UTC would be a recipe for disaster. If our local timezone changes because of DST, the event will have to move with it, doesn’t it?
For example, if we stored the UTC time of the kick off a month prior, we would naively do it using the current timezone of GMT +1. It would be stored as 20:00 UTC. The problem comes after retrieving the timestamp.
If we take out that 20:00 UTC from the database, and apply that day timezone GMT +2, the kickoff will be set to start at 22:00 local time, which is wrong.
The easiest solution to this problem would be to store the UTC timestamp as it was “local”, and its Timezone Region separately, because we don’t know which shift in time will have at that moment.
But we do know.
That problem is already solved
For our convenience, PHP is DST-aware. In other words, we can make a date with a local time under a timezone, as long that it’s the name of the region instead of hours, and PHP will apply the correct time shift.
To get a grasp of what we’re describing, lets use vanilla PHP for a couple of lines.
When we create a DateTime object, the time issued is treated as UTC. We can use a second parameter to instruct PHP that the time issued is under a Timezone Region with a DateTimeZone object. We can also take advantage of PHP parsing capabilities and put the region as part of the string. Both work the same, by the way.
Here are three examples to see how PHP automatically compensates the hours.
- A timestamp for the UCL Final kickoff with just an hour shift.
- Another with the Timezone Region.
- one 3 months prior where the DST is not in effect
As we can see, PHP is aware of the DST changes only if we apply the Timezone Region, not the hours to shift. The date where the DST is not active it automatically shifts one hour less.
There are some weird edge cases on DST to note, which also happens in the Javascript world, but nothing too important if you don’t expect working around the borders of DST changes.
Most operative systems and programming languages use the IANA Database for timezones, and usually any time conversion is DST-aware. Always check the docs if these shifts are supported, and any law that may change them in less than a year.
Since we’re under Laravel, we can use Carbon (or the Date façade) to do the same. I prefer the façade, though.
What about persisting the date with a timezone in the database?
MySQL 8.0.19 support adding the timezone, as well SQL Server 2016 and PostgreSQL 7.1. Meanwhile, both MariaDB and SQLite 3 doesn’t and that means you have to store the Timezone Region separately.
If PHP, Javascript, Rust, Go and other languages automatically shift the DST for a datetime when applying a Timezone Region, then, storing the timezone is not needed after all, right?
Right!
UTC reigns once again
We know that PHP will take into account the time shift of DST if the date is set with a timezone. In other words, if we apply the shift to the date we should get the immovable UTC timestamp of when the event will occur.
To put everything into perspective, let’s say the UEFA official is setting the date of the UCL Final in your app. We have two alternatives for receiving the timestamp from the frontend:
- Receive the local datetime with its timezone.
- Receive the UTC datetime.
In both cases, a little Javascript will do wonders for us. Alternatively, we can just one-off Luxon.js.
In the first specific case of the of receiving a non-UTC date, we will need to transform it to UTC before saving it — in the frontend or the backend, you decide. Otherwise, by skipping this step, we will persist accidentally the local time of 21:00 as it where UTC.
Laravel won’t normalize dates because it expects you’re using UTC at all times.
It’s usually common sense to format datetimes to ISO 8601, which both Javascript and PHP understand back and forth. In a Controller, we can easily validate this format by just putting Y-m-d\TH:i:s.uP, as PHP manual states.
We can make an additional step to ensure the date is always UTC, regardless of the hours difference the user is setting.
In any case, just be sure to know what you’re getting and if you need to transform it into UTC or not. Sometimes it’s not necessary, but you can still double down if you expect time coming from multiple entry points.
From there, you can persist it to the database like it was Sunday, and done!
Now that we only need to store the time in UTC and we know it won’t move unless politics get in the way, we don’t need to make any other further calculations.
Most of (sane) countries tend to make make DST changes very well ahead of time, so this gives software plenty of time to propagate changes receives from the IANA— just another reason why you should keep your software updated.
We only need to retrieve it and show it to the user in its local time. Hell, even a countdown will work without hiccups!
As a shoutout, be sure to check read Jon Skeet take on saving UTC time. Also, always keep at hand Carbon docs and Luxon.js docs if you want to work with datetimes without breaking a sweat.
