Just store UTC? Handling Time Zones & Daylight Saving

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

💥 Join this channel to get access to source code & demos!

🔥 Don't have the JOIN button? Support me on Patreon!

0:00 Intro
0:36 Saving as UTC
2:17 Time zones & Daylight Saving Time
4:17 Time Zone Database

#softwarearchitecture #softwaredesgin #codeopinion

[mabdullahsari]

Europe/Kiev is no longer a valid timezone in 2022b. Perfectly timed video.

[eranjeneabeysinghe]

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.

[FlaviusAspra]

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.

[umairahmedkhan]

Great job!
just the kind of channel pro's looking for YouTube is flooded with all the basics but hard to get the in depth knowledge of things, keep it up

[brnto]

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.

[cyscott]

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).

[kavunr]

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!

[georgehelyar]

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.

[raghuveerdendukuri]

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 👍

[jnn.nvtt]

Finally someone explained that easily to understand 👍

Most people won't even distinguish between a date and a date + time. Great video.

[appstratum]

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.

[anatolia]

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!

[kavunr]

I want to see the ValueObject for this 😀

[pablocom]

Very interesting, thanks for sharing!

[jensingels]

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.

[krccmsitp]

It's time to start using the universally universal Stardate. 🙃😉

[hnbright]

Insightful. Is there anything within the MSSQL Server 2019 that can help us achieve the same result, instead of using other databases you mentioned?

[mhDuke]

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.

[ThomasValadez-tv]

Isn't UTC a timezone. Why can't you store them with that timezone? The client needs to parse the time and apply the offset whether you supply Eastern Time or Universal Time. However universal time will likely not change it's timezone ever.