4 ms·
No. ALWAYS store user-entered dates in UTC, and simultaneously either (a) save the user's timezone preference or (b) use their system-specified timezone and onl
by dheera 7y ago
No. ALWAYS store user-entered dates in UTC, and simultaneously either (a) save the user's timezone preference or (b) use their system-specified timezone and only do that conversion at the last step when rendering the UI, preferably with client-side timezone functions. Also use client-side timezone functions to provide the server with UTC dates directly.
You'll deal with DST hell on your server otherwise. Countries change their DST and timezone policies from time to time, and you don't want to have to deal with keeping track of that stuff or force upgrading your server for a petty politician/timezone issue. That's the client machine's job.
Saving everything in UTC also makes comparisons, logging, abuse tracking much easier.
- wvenable 7y agoIf you use the client's timezone preference to store the date and then use the client's timezone preference to recover the date at a future date, you might still mess it up (from the user perspective). It depends on the user's intent. If they book something at 4:00pm 2 years from now, will that become 5:00pm if the timezone rules change or stay at 4:00pm. I'd argue that for most future-dated items it should stay at 4:00pm. The only way to guarantee that is store the local time. Storing UTC doesn't relieve you of timezone problems.
- stouset 7y agoFuture times record intent, which requires storing a datetime (sans any kind of time zone information) and location. Past times can be stored as a UTC timestamp. Storing and using a time zone for points in the future is the path of madness. If a user has an event at 2pm six months from now, they almost certainly want that event to be at 2pm no matter what the UTC offset happens to be on that day. This becomes even more obvious for repeating events: an event at 2pm this week will still be at 2pm six months from now, even after DST flips. And even if my government decides to change the time zone my location is in, or decided to opt into/out of DST.
- lhorie 7y ago> If a user has an event at 2pm six months from now, they almost certainly want that event to be at 2pm no matter what the UTC offset happens to be on that day Not necessarily. If the event is a long distance meeting that span multiple timezones, then by definition, the event isn't going to be at 2pm for all consumers of that data. Location is kind of a proxy for timezone, but it makes more sense to just let the client's OS figure the offset from UTC time than to manually manage timezones in the server yourself. Similarly, you let the client tell the server what timestamp "2pm two weeks from now" is (letting it consult its OS' timezone database to figure out what that means for the user's chosen geographic location), rather than trying to naively add up the seconds from now to then in the server. Saving the user's location in your DB to compute offsets on the server is problematic because you can't always get geographic data from the client (e.g. browsers will readily make timezone available to Javascript, but geolocation data might be unobtainable due to permissions/privacy concerns/etc). Also, users may move/travel, so getting geo data from side channels such as sign up forms also could lead to discrepancies.
- wvenable 7y agoIf you store UTC, you automatically get the result that meeting occurs at the same time for everyone. But you could also get the situation where, depending on timezone and DST changes, that 2:00pm meeting doesn't occur at 2:00pm for any of the participants! The problem is really far more difficult and interesting than just storing all dates as UTC as a rule.
- lhorie 7y agoAFAIK that can only happen if the the government changes the DST policy (or timezone) between the time the event was scheduled and the meeting date. Thankfully, DST policy changes are comparatively rare. And in actuality, even if the event doesn't happen at 2pm for the person who got affected by the DST policy change, it'll still be scheduled at the "expected" time for everyone else (unless they too get affected by a DST policy change as well). Also, most OSes are decent enough nowadays to update their timezone/DST databases regularly so that UIs that use UTC will reflect the discrepancy in future time intent soon after the policy change takes effect.
- dragonwriter 7y ago> Future times record intent, which requires storing a datetime (sans any kind of time zone information) and location. This assumes particular kind of intent, but not the only kind of intent future times might represent. Datetime + named time zone is often the actual intent (e.g., often for TV schedules), datetime + whatever the then-current legal time is at some particular location is, as you note, also often an intent, but it's not the only possible one. Note also that either of those may not be unique, depending on whether the named zone is offset specific or includes DST shifts, due to DST and other rarer sources of duplicate times in one zone or location , and the intent often is for a unique time, so you may actually need to store even more to capture intent.
- ShamelessC 7y agoCan you clarify? If I've seen an ad for a TV show saying it's going to air at 6 PM CST, I'm going to miss the show if it's actually on at 5 PM CST because the DST law changed or what have you.
- reitzensteinm 7y agoWait, you're using "Countries change their DST and timezone policies from time to time" as an argument for storing UTC in the database? As far as I'm concerned, this is the only valid argument against UTC. If a user adds an event for e.g. 2pm March 25th 2022 local time, you convert it to UTC, and then later the conversion rules for that day change, you now have a date that's been improperly converted to UTC. I don't think it's a strong enough reason not to use UTC in the db, given the multitude of issues surrounding other choices, but I do think that it's important to be clear about the pros and cons.