Just store UTC? Handling Time Zones & Daylight Saving
Vložit
- čas přidán 22. 06. 2024
- Should you store dates & times in your database as UTC? It's pretty standard advice if you're working in a system that needs to record dates and times from many different time zones. But this advice doesn't really hold true when you're dealing with dates and times in the future; here are some things you need to consider.
🔗 EventStoreDB
eventsto.re/codeopinion
🔔 Subscribe: / @codeopinion
💥 Join this channel to get access to source code & demos!
/ @codeopinion
🔥 Don't have the JOIN button? Support me on Patreon!
/ codeopinion
📝 Blog: codeopinion.com
👋 Twitter: / codeopinion
✨ LinkedIn: / dcomartin
📧 Weekly Updates: mailchi.mp/63c7a0b3ff38/codeo...
0:00 Intro
0:36 Saving as UTC
2:17 Time zones & Daylight Saving Time
4:17 Time Zone Database
#softwarearchitecture #softwaredesgin #codeopinion - Věda a technologie
Europe/Kiev is no longer a valid timezone in 2022b. Perfectly timed video.
Great job!
just the kind of channel pro's looking for CZcams is flooded with all the basics but hard to get the in depth knowledge of things, keep it up
As a lead developer this is just the youtube channel to get in touch with the common issues we ponder through. I gotta hand it to you man, it's such a great job you are doing... and I am a big fan of this channel.
Thanks! Glad you enjoy it.
You make it sound like you’re an aristocrat wandering the streets under disguise to see how the peasantry lives lol.
Cool ideas, never thought about storing timestamps in the future.
For the address though, I would store the lat and long, because addresses, zip codes, and even countries can change for a given location.
Yes, just an example. I do store lat/lng along with iana. Google maps also has a timezone api to provide lat/lgn and get back the iana tz
This is awesome! A nice quick summary and explanation of handling future dates and times. Great tips! Love this! Never seen it explained so simply and concisely.
Thanks!
I tried to create a ValueObject to encapsulate some of this, and found that a local time (without an offset) cannot capture the difference between:
- 2022-11-06T01:00 EDT (2022-11-06T05:00Z)
- 2022-11-06T01:00 EST (2022-11-06T06:00Z)
So if you care about this double 1am on the fall DST change day, you have to capture either UTC or a DateTimeOffset. I do like your recommendation of storing the Iana time zone and the db version, though!
Great video as always. Thank you!
Glad you enjoyed it!
Finally someone explained that easily to understand 👍
Most people won't even distinguish between a date and a date + time. Great video.
Great session, thanks.
You bet
Very interesting, thanks for sharing!
Thanks for watching
UTC has always been fine for me with storing a point in time. However, when you have concepts like 'mondays at 9am' then you need to know about time zones, daylight savings, etc.
The thing that caught us out was that Windows and Linux store time zones differently so code what was originally developed on Windows did not just run in a Linux container without some reasonably large changes.
Yes, I generally only use iana and not windows time zones.
There is one edge case where future timestamp should be stored without local time, and it is timestamps that aren't bound to a physical location (i.e. scheduling a message to execute in the future).
I've come across a lot of these kinds of problems/solutions for future scheduling in clinical trials, broadcast scheduling, etc. Including situations where clinical trials participants and health professionals (such as dieticians and clinical staff) are situated in different states with timezone differences between the two but both future and past events need to be recorded. Likewise situations where people are moving into different timezones temporarily for business/leisure and both future (scheduled) and past (diary) events need to be recorded.
This stuff is non-trivial and needs this kind of thought put into it. LIke others here have mentioned, recording of geolocation/geopoints is an option I'd always consider.
Another solid video. Thanks.
Thanks for the comment and insights. It is indeed non-trivial and once you start realizing you can't just "store it in UTC".. you start seeing a whole pile of issues you need to address.
I want to see the ValueObject for this 😀
We've been doing the same thing by using NodaTime for years and works like charm :) We also have a batch checking tzdb version for any timezone rule change to recalculate UTC dates. Thanks for the great explanation!
Ya, NodaTime FTW
I always save datetime in UTC ingeneral and sometimes local time with timezone (previously +05:30 kind of for IST and now Asia/Kolkata), as you mentioned but, never tried storing the database version, need to check if we have such option/requirement in PHP applications.
Thanks for the headsup 👍
Why isn't there a global standard to address this particular issue yet? A new time format datatype could solve this up to the infrastructure level.
Insightful. Is there anything within the MSSQL Server 2019 that can help us achieve the same result, instead of using other databases you mentioned?
If persisting application event timestamps to the database in utc couldn’t that lead to conversion issues on the frontend. For example, a user views an event that has a timestamp of 2022-08-08T20:00:00Z. If the user views the event during their local time zone with DST and a -5:00 offset that will convert fine but if they view the same event again when DST has ended in their local time zone then the offset would be -6:00 which would make that event in the past occur an hour earlier then when it actually did. Is this sound correct?
it's not local to current time but local to timezone. and given utc time is precise in given timezone. it knows to which offset it relates
It's time to start using the universally universal Stardate. 🙃😉
You're right, is it overkill in most cases though?
Asides from tzdb version which can introduce new rules, how do you detect when a timezone switch to DST ? Do you use a daily batch to check these changes? Any sample code ? It would be very interesting.
Thank you very much for your time, I really appreciate your videos
Not sure why you would want to check when a tz switches to DST? Ultimately with something like NodaTime you can determine if you are in DST with now.InZone(zone).IsDaylightSavingTime()
@@CodeOpinion okaay I see now, thank you very much
what about storing time in the future as just a fixed date and time. the train will move at 23:00 of Jan 1st, next year from london central station. now even if I live in a different continent, all I need to do is be at london central station prior to 23:00, even if daylight saving time changed from now till next Jan 1st, the train will depart at 23:00, no matter what.
Save future date times as just date times with no information other than where the event is going to happen.
This is probably my preferred method. As MSSQL already supports the Date and the Time format, it makes sense to just store a Date/Time/Location(inna name) for a future event date. If you need to be able to do extensive queries/comparisons then also store the future date as a full DateTime in utc format.
Clear as mud 🙃
I was born on November 29 at 11:30pm in Waco Texas. At that same moment in New York, it was November 30 at 12:30 in the morning. If I were to move to New York could I say that my birthday was now November 30?
Ha. That's interesting but my answer would be no since its based on the local time of where the event took place.
I don't get why do you also record local instead of just utc+version ?
I was also wondering if there isn't some redundancy in the data. I don't really have the answer but I imagine it is important to explicitly store what the user's intention was. The user expects something to happen eg. at 18:00 local time on a specific day in the future, independent of the applicable time zone rules on that day. However DST changes by then, the event should still happen at 18:00 local time, whatever that will be in UTC. So, that is the first thing to save and keep in the DB. It also means, you can use that information independent from the capabilities of the time library you are using by then. Cause that might also change. Or the version you use then just might be broken.
I used to think that datetimeoffset was the swiss army knife Obviously not
ya, it just gives you the absolute time. Just a moment in time that is the same for everyone. However, we are often more interested in calendar time which has to live by time zones and daylight saving rules.
Can't you just save UTC, and then whenever a client queries it, convert it to their current local time?
If you think about applications like shared calendars with users in different time zones, this should work as expected. It will keep meetings with people across different time zones in sync.
It won't keep meetings with people across different timezones in sync, unless you also add a timezone for the meeting to follow.
@@benwoodward3446 The meeting will follow UTC :D
Meeting end time will be incorrect if DST has occurred between meeting start time and end time, unless you also record timezone offset in addition to UTC and using it in the app layer.
Why use UTC only and not local time with time zone info?
Like Derek said, UTC is very handy for reporting, querying or ordering purposes. If you don't need this kind of operations, you can just store local datetime, timezoneId and tzdb version for future dates
Exactly.
local time and time zone can lead to ambiguity during daylight saving change
ISO-8601 :D
Timezone stands for headache :/
I agree
I think it's one of the hardest problems in software engineering.
@@anatolia23 indeed
Global usage of UTC as THE (only) timezone when? 😭
Yeah, and it will just be time with different meanings. Like I woke up AT 9 AM noon. and in the other side, I woke up 9 AM midnight, that's fine x) we can adapt
Do you know why Microsoft didn't use IANA Timezones until .NET 6?
I'm not really sure. I just defer to Nodatime
Need to be careful with that. .net 6 only supports them if the operating system does. I can't remember the exact version but it's the last 2 years or so of Windows 10. So even a fully updated Server 2019 (what we run on) doesn't support iana ids.
Why not use timestamps? You could do conversion on the spot and querying with timestamps is possible too, even seamless if your ORM supports conversion before query.
Timestamps don't solve the issues with future dates, unless saved along with additional information - time zone and db version, so it's no more than a matter of presentation/formatting.
Correct, timestamps have the same issue. It's just a representation .
Do they? Timestamps aren't a representation of a date but instead it's an amount of time passed since a point in history. Timestamp will never skip a second or go back any amount, no matter what happens with the time zones. 30 days is always 2592000 seconds, no matter the time zones.
Tool that handles conversion from timestamp to current time (and vice-versa) is the thing that accounts for leap days, skipped days, changes in time zones etc. So if there was a change in UTC and it's now shifted by 1 hour, timestamp stays as it was but the tool now "adds an hour to the timestamp" every time it converts.
@@PiesekLeszek90 Yes, they do. The fact that timestamps are just a number of some time intervals since the start of an arbitrary epoch is precisely why they don't work for future dates.
The tool that converts a timestamp to a date will perform the conversion according to the time zone rules at the time of conversion, not at the time you created the timestamp with the intention of representing a specific date and time in the future.
The conversion tool, of course, has no idea what were the rules when you first recorded the timestamp, because it has no idea when the timestamp was recorded (because you haven't saved this information alongside the timestamp), so it cannot deduce what local time the timestamp meant to represent and how many intervals it should add to the value.
So if you want to preserve the future date as it was entered, especially with the intention to compare it with other dates, you have to store additional information alongside it, e.g. the time zone rules at the time of the timestamp creation.
At this point it really doesn't matter how you represent the intended date: as an ISO string, or as a numeric timestamp - you still need to know what were the rules at the time it was recorded in order to apply the rules known at the time, thus reproducing the originally intended date/time.
To be clear, I'm talking about unix timestamp which is always since 01.01.1970 UTC, so no matter if I record the timestamp in utc+0 or utc+8, they both will be the exact same number (assuming i recorded them in the same moment in time)
That's what's powerful about unix timestamp, you don't need to record at what rules it was recorded because it being a timestamp implies it's an amount of seconds since 01.01.1970 UTC
Don't confuse timestamp as representation of a date because it's not, it's a representation of time passed and passage of time isn't affected by arbitrary rules created by humans.
For example, you save a timestamp with 30 days since "now", then after 30 days you read it, even if utc moved or whatever happened to calendar, the conversion tool should know that it did happen since 1970 and apply the change correctly.
I think for the past it is not so simple as well. For some objects or events, which are tight to a physical location, we need to save the local time too.
Example: Photo library. I, as a photographer, took a photo in Belgium on Wednesday at 08:00 and uploaded it to the library. Later, when I am traveling over the US east coast, I want this photo to be still shown as taken on Wednesday at 08:00. For this case, if the photo date was stored in UTC, it would be shown as Tuesday 23:00.
Z is Zulu time, not Zero en.wikipedia.org/wiki/List_of_military_time_zones
Yup. NATO phonetic alphabet. It's also often referred to as Z-Time and Zero Time, none of which I ever really referred to as but I have heard those.
Nice video. I worked with Zabbix and the system register time as epoch. I liked the idea after I understand how it works.