Created
June 24, 2018 19:15
-
-
Save DannyvdSluijs/ac5e0e1ea6c7a5d5c3000008222044c8 to your computer and use it in GitHub Desktop.
Potential loss of zonetime info storing DateTime and DateTimeZone in DB
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| echo (DatetimeImmutable ::createFromFormat ('U', 1540686600, new DateTimeZone('UTC'))) | |
| ->setTimeZone(new DateTimeZone('Europe/Amsterdam')) | |
| ->format('d-m-Y H:i:s (e P)'); | |
| // 28-10-2018 02:30:00 (Europe/Amsterdam +02:00) | |
| echo (DatetimeImmutable ::createFromFormat ('U', 1540690200, new DateTimeZone('UTC'))) | |
| ->setTimeZone(new DateTimeZone('Europe/Amsterdam')) | |
| ->format('d-m-Y H:i:s (e P)'); | |
| // 28-10-2018 02:30:00 (Europe/Amsterdam +01:00) | |
| echo (new DatetimeImmutable('2018-10-28 02:30:00', new DateTimeZone('Europe/Amsterdam'))) | |
| ->format('d-m-Y H:i:s (e P)'); | |
| // 28-10-2018 02:30:00 (Europe/Amsterdam +01:00) | |
| // How would we have stored the first case as DateTime and DateTimeZone values using an DST observing timezone ?? |
Author
Thank @heiglandreas for that detailed reply. And reading your blog it seems scary indeed (Also knowing that we store all in UTC, mainly as the data comes from computer systems, IoT sensors and GPS devices and already is in UTC). I find peace in the fact that we only store historical timestamps e.g. no political DST shifts that jump out and surprise us. Assuming we need to keep the the timezone-database up to date.
Thanks again for share with me and improving my knowledge on timezones.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is indeed an issue that I try to avoid during the talk as it's usually an edge-case and complicates things even more ;-)
Currently the DateTime-Library in PHP doesn't allow you to set a timezone in the constructor (or via
createFromFormat) when an offset is explicitly given with the datetime. So callingnew DateTime('28-10-2018 02:30:00+02:00', new DateTimezone('Europe/Amsterdam'))would result in a DateTime-Object that has a timezone of+02:00and not the expectedEurope/Amsterdam. It's important to note, that a timestamp also has an explicit offset of +00:00 set. So callingnew DateTime('@ 1540686600', new DateTimezone('Europe/Amsterdam'))will completely ignore the second parameter and the timezone will be set to+00:00(not even UTC!).So how to resolve this mess?
You will need to store the offset as well. And then initialize the DateTime-Object with the offseted datetime and after that set the timezone to the requested one. Then you are absolutely safe. I've created a short example at https://3v4l.org/WjYTi - but please note that HHVM misbehaves…
This has one caveat though! Internally that is a timezone-conversion. Ideally from
+02:00to+02:00so none at all. But what happens when you are storing a future event with an offset of+02:00and then – when the actual datetime arises that offset is not valid anymore due to the governement suddenly deciding to not use it anymore? In the end it's the same issue you will have like when converting to UTC. I've written a blog-post why that is a bad idea.So the for me more important question is: Why do you need to store the information that something is happening during that transition-hour? Isn't that more relevant when you have data that is kind of a logging-information? And if that is the case, wouldn't it be easier to actually store the UTC-datetime or even a timestamp (which would be the same at that point)? So I'D really be interested in your specific use case that requires that information… ;-)
Does that help?