2 ms·

You seem to be missing the forest for the trees. DST policy changes _are_ rare, compared to the millions of times when people schedule multi-timezone meetings,

by lhorie 7y ago

You seem to be missing the forest for the trees. DST policy changes _are_ rare, compared to the millions of times when people schedule multi-timezone meetings, international flights, etc. Bursts of high frequency DST policy changes are so rare that they get articles written about them. > you cannot correctly convert that UTC date back to local time. Of course you can. It may not be the same local clock time with most implementations, sure, but it will still correctly point to the same physical time relative to the local times of everywhere else on Earth. It would be physically impossible to retroactively change the time of the meeting to account for the the DST policy change and maintain the same clock time for participants in other timezones, so something's gotta give. It's also not true that you can't recover the intended time after a DST policy change. Although it's monumentally laborious, it's definitely possible: you can keep track of the historical changes in the timezone database and work backwards from the UTC date and the date of that record change. > By converting a future date to UTC, you're making the assumption that the timezone information you have for that future date won't change from now until that date passes. Sort of. The assumption there is that the time delta between now and then is smaller than the delta between a notice of policy change and the time it goes into effect. It is, however, technically an engineering trade-off. The trade-off here is that you can have a far simpler and almost-always correct implementation by taking a tiny risk of a discrepancy between intent and actual local time in the extremely rare occasion that a policy changes without advance notice. Responsible governments will typically give plenty of advance notice so timezone databases can be updated and clients can then correctly calculate future offsets since they will know how to account for the policy change. You could certainly forego UTC time and make an engineering decision of storing local clock time if your application matches a very strict set of timezone boxing requirements, but in my experience, it's more common that people pick this approach out of ignorance than careful analysis, and in some cases, it gets nasty to support when the requirements change (e.g. as soon as you have a new sales team office on the other side of the country)

wvenable 7y ago
Why go through all this just to follow the rule of storing UTC in the database? At what point does it just become the wrong thing to do? Storing local time and timezone in the database for future dates is easy. It has none of these issues. It produces the correct results that people expect. You can convert to UTC at anytime.
- lhorie 7y ago
  Storing local time is fine if you and your server never ever leave your timezone. You can even get away with it for a long time. If your logic does ever require timezone conversions down the line though, it will most likely surface some nasty problems. And when I say nasty, I mean it in the will-leave-a-battle-scar-that-your-future-graybeard-self-will-warn-younguns-about sense. The thing with the UTC advice and those who don't listen to it is that people often don't realize that both DST policy changes and factors that increase the number of applicable timezones in a business are rare to begin with. They often think that that storing local time is safe because they never had to truly deal with the complexities of representing physical time. But DST policy changes are much rarer than factors that fuck up local time based implementations, and issues can be fixed without huge schema changes and downtimes. Plus it's less likely you will actually need to fix them since higher-ups won't be able to grasp the issue, whereas a local time bug fix will often come as a demand from some higher-up suit who flew out of state.
  - wvenable 7y ago
    I've said all over this thead to store local time and the associated timezone. You do that and no nasty problems can happen. And you are correctly storing the time that the user intended and not the time it might be converted into.
    - lhorie 7y ago
      If you're storing the timezone (and by that I assume you're doing so unambiguously, because offset alone doesn't tell you hemisphere/DST rules, and timezone names are not unique), then you're effectively using UTC, albeit storing it in a non-normalized format. The flaw in your logic is in assumption that "intended time" is always fixed relative to one user's clock time. For international events, intended time is physical time. If it's a friday and I verbally arrange a meeting with someone in Sao Paulo at 3pm BST the following monday and that translates to 11am PST today for me at San Francisco, but if there's a DST change for me over the weekend, the intended time is still 3pm BST, not 11am PST, because in such cases, the general logical expectation is that whoever is getting a time change is the one who should be on top of it. In either case, you'd be able to recover "intended time" (for any meaning of the term) 99.9999% of the time regardless of whether you used UTC or local time+timezone. If there was indeed an unlikely event of an unannounced DST policy change, then whether intended time is wrong depends on who's affected. If it's an international meeting and intended time means physical time, then local time+timezone does not capture true intended time. Likewise, if intended time means local time for a single user, UTC will be the wrong one. But as I said, this case is vanishingly unlikely. A lot of code doesn't handle integer overflows correctly because they are unlikely too and the world is still humming along just fine.
flukus 7y ago
> You seem to be missing the forest for the trees. DST policy changes _are_ rare, compared to the millions of times when people schedule multi-timezone meetings, international flights, etc. Bursts of high frequency DST policy changes are so rare that they get articles written about them. It's not just DST policy changes. If I want to run a report at 10PM every day in my timezone and we store that time as UTC then my report will be running at 11PM after the DST change over, it will start screwing up twice a year. This does not happen when my local time and timezone are stored because the software working with the data can make smarter decisions with more information.
- lhorie 7y ago
  > screwing up Is it wrong just because the clock time is wrong? Or is the data actually going to get corrupted? In my experience, reports are usually reporting on a period of time, and it doesn't actually matter if the job runs an hour off since you don't want them to be time sensitive in the first place (e.g. you might get noise because some query became very expensive for some reason and delayed a portion of the job by an hour, or the job ran in a data center in a different state, etc). Besides, this isn't really the same class of problems that the "use UTC" advice targets. Crons are a far better tool for daily scheduled jobs. The UTC advice is for systems that communicate dates across multiple machines.

by lhorie 7y ago

wvenable 7y ago

Why go through all this just to follow the rule of storing UTC in the database? At what point does it just become the wrong thing to do? Storing local time and timezone in the database for future dates is easy. It has none of these issues. It produces the correct results that people expect. You can convert to UTC at anytime.

lhorie 7y ago
Storing local time is fine if you and your server never ever leave your timezone. You can even get away with it for a long time. If your logic does ever require timezone conversions down the line though, it will most likely surface some nasty problems. And when I say nasty, I mean it in the will-leave-a-battle-scar-that-your-future-graybeard-self-will-warn-younguns-about sense. The thing with the UTC advice and those who don't listen to it is that people often don't realize that both DST policy changes and factors that increase the number of applicable timezones in a business are rare to begin with. They often think that that storing local time is safe because they never had to truly deal with the complexities of representing physical time. But DST policy changes are much rarer than factors that fuck up local time based implementations, and issues can be fixed without huge schema changes and downtimes. Plus it's less likely you will actually need to fix them since higher-ups won't be able to grasp the issue, whereas a local time bug fix will often come as a demand from some higher-up suit who flew out of state.
- wvenable 7y ago
  I've said all over this thead to store local time and the associated timezone. You do that and no nasty problems can happen. And you are correctly storing the time that the user intended and not the time it might be converted into.
  - lhorie 7y ago
    If you're storing the timezone (and by that I assume you're doing so unambiguously, because offset alone doesn't tell you hemisphere/DST rules, and timezone names are not unique), then you're effectively using UTC, albeit storing it in a non-normalized format. The flaw in your logic is in assumption that "intended time" is always fixed relative to one user's clock time. For international events, intended time is physical time. If it's a friday and I verbally arrange a meeting with someone in Sao Paulo at 3pm BST the following monday and that translates to 11am PST today for me at San Francisco, but if there's a DST change for me over the weekend, the intended time is still 3pm BST, not 11am PST, because in such cases, the general logical expectation is that whoever is getting a time change is the one who should be on top of it. In either case, you'd be able to recover "intended time" (for any meaning of the term) 99.9999% of the time regardless of whether you used UTC or local time+timezone. If there was indeed an unlikely event of an unannounced DST policy change, then whether intended time is wrong depends on who's affected. If it's an international meeting and intended time means physical time, then local time+timezone does not capture true intended time. Likewise, if intended time means local time for a single user, UTC will be the wrong one. But as I said, this case is vanishingly unlikely. A lot of code doesn't handle integer overflows correctly because they are unlikely too and the world is still humming along just fine.

flukus 7y ago

> You seem to be missing the forest for the trees. DST policy changes _are_ rare, compared to the millions of times when people schedule multi-timezone meetings, international flights, etc. Bursts of high frequency DST policy changes are so rare that they get articles written about them. It's not just DST policy changes. If I want to run a report at 10PM every day in my timezone and we store that time as UTC then my report will be running at 11PM after the DST change over, it will start screwing up twice a year. This does not happen when my local time and timezone are stored because the software working with the data can make smarter decisions with more information.

lhorie 7y ago
> screwing up Is it wrong just because the clock time is wrong? Or is the data actually going to get corrupted? In my experience, reports are usually reporting on a period of time, and it doesn't actually matter if the job runs an hour off since you don't want them to be time sensitive in the first place (e.g. you might get noise because some query became very expensive for some reason and delayed a portion of the job by an hour, or the job ran in a data center in a different state, etc). Besides, this isn't really the same class of problems that the "use UTC" advice targets. Crons are a far better tool for daily scheduled jobs. The UTC advice is for systems that communicate dates across multiple machines.