4 ms·

Yeah, "always use UTC" is overrated. It's useful for things that have happened, but quickly becomes unintuitive for things that will happen - including birthday

Yeah, "always use UTC" is overrated. It's useful for things that have happened, but quickly becomes unintuitive for things that will happen - including birthdays, meetings, ...

mattmanser 5y ago
It can even become wrong for future dates. Always use UTC is a broken, bad rule for future dates, it only makes sense for things that have already happened. As for how it breaks, you have a booking for 8pm the day after the start of summer time. You stored the date/time in UTC. Government changes the start of summer time. Your database now thinks it's at 7pm because it uses UTC. Here's some examples: https://codeofmatt.com/on-the-timing-of-time-zone-changes/ https://codeofmatt.com/on-the-timing-of-time-zone-changes/ Also the EU is seriously debating getting rid of clock changes, and it comes up in the UK semi-regularly. Your database will not fix your bad data when that happens.
- mulmen 5y ago
  But would using local time help in this case? For example, what if the future event is something like the solstice? It's usecase dependent so it seems like the application should deal with this, at which point persisting in UTC is still a reasonable choice.
  - DasIch 5y ago
    When you schedule an event in the future like “meet me at 14:00 in cafe foo”, this means local time and it’s going to mean 14:00 local time even when the timezone changes. If you store this in UTC and the timezone changes, you end up converting to the wrong time. So for future events you should store local time with a location you can map to a timezone.
    - mulmen 5y ago
      The solstice is based on astronomical phenomena. If you store it in UTC it will always be right.
      - funcDropShadow 5y ago
        Unless there is a leap second scheduled on short notice or you install the updated tz database rather late. For astronomical events or long term measurements (think CERN) there is astronomical time, which does not add leap seconds. But if you want to have precise date time far in the future you must not use UTC based milliseconds representations unless you want to check for necessary database migrations every time the time zone definitions change.
        deleted 5y ago
        [deleted]
        mulmen 5y ago
        If you store zoned time in UTC then the zones or UTC change then the time can be formatted correctly in the future. My point in mentioning the solstice is that while some events are at “2pm”, others are “when something happens”. Sure, UTC is not suitable for conducting astronomy. It is suitable for working with human friendly times, since it is the foundation of timezones.
- nicoburns 5y ago
  Yep! It sounds like overkill, but the suggestion I've seen that seemed to cover all bases was to store: (1) The time in the local timezone (+ the timezone itself) (2) The UTC conversion (3) The version of the IANA database used to compute (2) Then you can use (2) the UTC time for most computations/queries, but if a timezone changes then you can use (3) to tell which UTC dates need to be updated, and (1) to recompute the new correct version of (2).
  - mattmanser 5y ago
    That seems like overkill for most apps. Better to just store the date/time without timezone, and either the windows or linux timezone name of the location (which has far more specificity than the ISO 8601 offset). Then calculate the actual time on the fly when pulling it out of the DB. No mucking around with IANA databases, everything's easy to program, server usually keeps itself up-to-date.
- stickfigure 5y ago
  Even more common: * Your meeting is scheduled for 2pm tomorrow. * Oh oh! Your meeting got bumped to the next day. * Daylight savings switched overnight. Some things are measured in relative time. Some things are measured in absolute time. Never confuse the two in your database.
- elgaard 5y ago
  Yes, timezones do not at all work with reservations for physical objects. I work on a registration and reservation system for rowing boats in my rowing club https://github.com/dsroklub/roprotokol https://github.com/dsroklub/roprotokol The only time that matters is whatever the clock in the clubhouse shows (Danish time) If you sign out a boat from the UK (or more realistically from a laptop that thinks it is in the UK) at 14:00 it is still 14:00 Danish time. This is surprisingly difficult to achieve with JS Date object, browsers and popular JS components. So the Temporal Plain time looks promising. And especially the explicit timezones. Because even without timezone you need to handle DST. For example my system needs to know that if you start a 3 hour trip at midnight a certain day in the spring, you are expected back at 04:00. I just hope that browsers and components will support is properly.
- no-s 5y ago
  > Your database will not fix your bad data when that happens. Because it’s not bad data, HaHaOnlySerious. I tell developers that if the database attribute accepts a value it must be in the domain of allowable values. Often people refuse check constraints in order to avoid expressing business logic in the database.
  - GormanFletcher 5y ago
    The GP's example doesn't involve invalid datetimes. The datetimes aren't out of bounds or invalid in any way that a check constraint would detect. They've just become factually incorrect ("bad data"), because they are derived data that wasn't updated when the derivation rules changed (i.e., regulatory changes). If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. Storing the time zone abbreviation (e.g., EST) is inadvisable, as computers sometimes care whether you asked for EST vs EDT. Storing the time offset (e.g., -500) is incorrect, as it has the same pitfalls as storing UTC - you're precomputing the locale's expected time offset at storage time, and your data won't automatically be corrected if time regulations change. If you're storing historical timestamps, UTC is fine because you can safely convert it to whatever time zone you want to display, knowing that changes to time zone / DST regulations tend not to affect the past.
    - no-s 5y ago
      > If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. At this point in the process first normal form flies out the window. Trying to generalize too much can lead you down some weird garden paths. If it looks like you need a function to validate an prospective column value then you probably need to model the value as a relation corresponding to the function parameters. Then you can make it into a foreign key constraint and get on with your project. I truly appreciate the efforts of those who attempt to expand the utility of datetime value representation to perfect a wider variety of denotational semantics. But with a relational model it may be better to delegate to simpler abstraction sufficient to the specific case.
dheera 5y ago
Personally I solve this problem by just doing everything in UTC. All my clocks, calendars, phones, everything read UTC. "Always use UTC" works if you really, really always use UTC.
- thewakalix 5y ago
  What about interfacing with the outside world, which doesn't always use UTC?
  - dheera 5y ago
    Translate. But frankly I often still use UTC to coordinate with people, especially when coordinating meetings with people in 3 timezones.
mianos 5y ago
Always use UTC is not overrated. It is true, it may be overrated if you don't store time with your date or don't have a datetime type in your database (that makes me think WTF). You are always better using UTC to store dates if you have a datetime type.
BurningFrog 5y ago
Always use UTC for timestamps. Never use UTC for dates. Like OP says, a simple YYYYMMDD string is better.
- drenvuk 5y ago
  This is wrong depending on the use case and where the user is and where the user might be going.

Yeah, "always use UTC" is overrated. It's useful for things that have happened, but quickly becomes unintuitive for things that will happen - including birthdays, meetings, ...

mattmanser 5y ago

It can even become wrong for future dates. Always use UTC is a broken, bad rule for future dates, it only makes sense for things that have already happened. As for how it breaks, you have a booking for 8pm the day after the start of summer time. You stored the date/time in UTC. Government changes the start of summer time. Your database now thinks it's at 7pm because it uses UTC. Here's some examples: https://codeofmatt.com/on-the-timing-of-time-zone-changes/ https://codeofmatt.com/on-the-timing-of-time-zone-changes/ Also the EU is seriously debating getting rid of clock changes, and it comes up in the UK semi-regularly. Your database will not fix your bad data when that happens.

mulmen 5y ago
But would using local time help in this case? For example, what if the future event is something like the solstice? It's usecase dependent so it seems like the application should deal with this, at which point persisting in UTC is still a reasonable choice.
- DasIch 5y ago
  When you schedule an event in the future like “meet me at 14:00 in cafe foo”, this means local time and it’s going to mean 14:00 local time even when the timezone changes. If you store this in UTC and the timezone changes, you end up converting to the wrong time. So for future events you should store local time with a location you can map to a timezone.
  - mulmen 5y ago
    The solstice is based on astronomical phenomena. If you store it in UTC it will always be right.
    - funcDropShadow 5y ago
      Unless there is a leap second scheduled on short notice or you install the updated tz database rather late. For astronomical events or long term measurements (think CERN) there is astronomical time, which does not add leap seconds. But if you want to have precise date time far in the future you must not use UTC based milliseconds representations unless you want to check for necessary database migrations every time the time zone definitions change.
      - deleted 5y ago
        [deleted]
      - mulmen 5y ago
        If you store zoned time in UTC then the zones or UTC change then the time can be formatted correctly in the future. My point in mentioning the solstice is that while some events are at “2pm”, others are “when something happens”. Sure, UTC is not suitable for conducting astronomy. It is suitable for working with human friendly times, since it is the foundation of timezones.
nicoburns 5y ago
Yep! It sounds like overkill, but the suggestion I've seen that seemed to cover all bases was to store: (1) The time in the local timezone (+ the timezone itself) (2) The UTC conversion (3) The version of the IANA database used to compute (2) Then you can use (2) the UTC time for most computations/queries, but if a timezone changes then you can use (3) to tell which UTC dates need to be updated, and (1) to recompute the new correct version of (2).
- mattmanser 5y ago
  That seems like overkill for most apps. Better to just store the date/time without timezone, and either the windows or linux timezone name of the location (which has far more specificity than the ISO 8601 offset). Then calculate the actual time on the fly when pulling it out of the DB. No mucking around with IANA databases, everything's easy to program, server usually keeps itself up-to-date.
stickfigure 5y ago
Even more common: * Your meeting is scheduled for 2pm tomorrow. * Oh oh! Your meeting got bumped to the next day. * Daylight savings switched overnight. Some things are measured in relative time. Some things are measured in absolute time. Never confuse the two in your database.
elgaard 5y ago
Yes, timezones do not at all work with reservations for physical objects. I work on a registration and reservation system for rowing boats in my rowing club https://github.com/dsroklub/roprotokol https://github.com/dsroklub/roprotokol The only time that matters is whatever the clock in the clubhouse shows (Danish time) If you sign out a boat from the UK (or more realistically from a laptop that thinks it is in the UK) at 14:00 it is still 14:00 Danish time. This is surprisingly difficult to achieve with JS Date object, browsers and popular JS components. So the Temporal Plain time looks promising. And especially the explicit timezones. Because even without timezone you need to handle DST. For example my system needs to know that if you start a 3 hour trip at midnight a certain day in the spring, you are expected back at 04:00. I just hope that browsers and components will support is properly.
no-s 5y ago
> Your database will not fix your bad data when that happens. Because it’s not bad data, HaHaOnlySerious. I tell developers that if the database attribute accepts a value it must be in the domain of allowable values. Often people refuse check constraints in order to avoid expressing business logic in the database.
- GormanFletcher 5y ago
  The GP's example doesn't involve invalid datetimes. The datetimes aren't out of bounds or invalid in any way that a check constraint would detect. They've just become factually incorrect ("bad data"), because they are derived data that wasn't updated when the derivation rules changed (i.e., regulatory changes). If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. Storing the time zone abbreviation (e.g., EST) is inadvisable, as computers sometimes care whether you asked for EST vs EDT. Storing the time offset (e.g., -500) is incorrect, as it has the same pitfalls as storing UTC - you're precomputing the locale's expected time offset at storage time, and your data won't automatically be corrected if time regulations change. If you're storing historical timestamps, UTC is fine because you can safely convert it to whatever time zone you want to display, knowing that changes to time zone / DST regulations tend not to affect the past.
  - no-s 5y ago
    > If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. At this point in the process first normal form flies out the window. Trying to generalize too much can lead you down some weird garden paths. If it looks like you need a function to validate an prospective column value then you probably need to model the value as a relation corresponding to the function parameters. Then you can make it into a foreign key constraint and get on with your project. I truly appreciate the efforts of those who attempt to expand the utility of datetime value representation to perfect a wider variety of denotational semantics. But with a relational model it may be better to delegate to simpler abstraction sufficient to the specific case.

dheera 5y ago

Personally I solve this problem by just doing everything in UTC. All my clocks, calendars, phones, everything read UTC. "Always use UTC" works if you really, really always use UTC.

thewakalix 5y ago
What about interfacing with the outside world, which doesn't always use UTC?
- dheera 5y ago
  Translate. But frankly I often still use UTC to coordinate with people, especially when coordinating meetings with people in 3 timezones.

mianos 5y ago

Always use UTC is not overrated. It is true, it may be overrated if you don't store time with your date or don't have a datetime type in your database (that makes me think WTF). You are always better using UTC to store dates if you have a datetime type.

BurningFrog 5y ago

Always use UTC for timestamps. Never use UTC for dates. Like OP says, a simple YYYYMMDD string is better.

drenvuk 5y ago
This is wrong depending on the use case and where the user is and where the user might be going.