3 ms·

Everything in the DB should be UTC. Anything else is crazy.

growse 6y ago
This isn't true. For example: If a user schedules an event to happen at 15:35 on July 29th 2021 IST, you cannot know with certainty what the equivalent UTC value is. You know what it would be assuming the relationship between IST and UTC stays the same between now and next year. If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. Timezones are political, but they're also how people organise their lives. The user's local timezone is context that you can't just discard.
- deleted 6y ago
  [deleted]
- int_19h 6y ago
  > If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. It is not wrong. When timezones change, the database that contains that info is updated - but it still retains information about historical rules, precisely so that earlier dates can still be converted reliably. On the other hand, if you use local times, then you are not able to distinguish between pre- and post-clock change, when the same time repeats twice.
  - tchaffee 6y ago
    How are you going to know to look for changes to IST when all you have is a UTC date?
  - deleted 6y ago
    [deleted]
  - growse 6y ago
    You're not converting a historic date though, you're converting a future date. And that's an inherently uncertain process. It is literally impossible to know what UTC time 2021-07-29T15:35:00 IST refers to until it happens. The Indian government is free to redefine IST as they wish, and could choose to do that at 15:34:59.
tchaffee 6y ago
Some parts of Brasil decided to not observe daylight savings this year. I had to file a bug with Chrome which was converting dates in JS off by one hour for some Brazilian cities. Firefox got it right. If a couple of years ago you had stored my future appointment in your db using UTC, your db would now have the wrong time for my Brasil appointment.
erikpukinskis 6y ago
Times, perhaps. But dates should never be stored with a timezone at all, except in extremely rare cases where a day for some reason specific to a timezone. But even then, it's much more likely that you are trying to specify a time range that happens to start and end on a certain day in a certain place. Dates, almost by definition, are independent of timezone. January 1st, 2020 began at several different times in different timezones. It should be represented as a date stamp: 2020-01-01 without any timezone. If you really are trying to represent the day that starts at 12:00 AM PST on January 1, 2020 and ends at 12:00 PM PST the same day, then you probably just want two timestamps representing those instants and not a date at all. But again, that would be quite rare. In most cases you would just want to store 2020-01-01.

This isn't true. For example: If a user schedules an event to happen at 15:35 on July 29th 2021 IST, you cannot know with certainty what the equivalent UTC value is. You know what it would be assuming the relationship between IST and UTC stays the same between now and next year. If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. Timezones are political, but they're also how people organise their lives. The user's local timezone is context that you can't just discard.

deleted 6y ago
[deleted]
int_19h 6y ago
> If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. It is not wrong. When timezones change, the database that contains that info is updated - but it still retains information about historical rules, precisely so that earlier dates can still be converted reliably. On the other hand, if you use local times, then you are not able to distinguish between pre- and post-clock change, when the same time repeats twice.
- tchaffee 6y ago
  How are you going to know to look for changes to IST when all you have is a UTC date?
- deleted 6y ago
  [deleted]
- growse 6y ago
  You're not converting a historic date though, you're converting a future date. And that's an inherently uncertain process. It is literally impossible to know what UTC time 2021-07-29T15:35:00 IST refers to until it happens. The Indian government is free to redefine IST as they wish, and could choose to do that at 15:34:59.

tchaffee 6y ago

Some parts of Brasil decided to not observe daylight savings this year. I had to file a bug with Chrome which was converting dates in JS off by one hour for some Brazilian cities. Firefox got it right. If a couple of years ago you had stored my future appointment in your db using UTC, your db would now have the wrong time for my Brasil appointment.

erikpukinskis 6y ago

Times, perhaps. But dates should never be stored with a timezone at all, except in extremely rare cases where a day for some reason specific to a timezone. But even then, it's much more likely that you are trying to specify a time range that happens to start and end on a certain day in a certain place. Dates, almost by definition, are independent of timezone. January 1st, 2020 began at several different times in different timezones. It should be represented as a date stamp: 2020-01-01 without any timezone. If you really are trying to represent the day that starts at 12:00 AM PST on January 1, 2020 and ends at 12:00 PM PST the same day, then you probably just want two timestamps representing those instants and not a date at all. But again, that would be quite rare. In most cases you would just want to store 2020-01-01.