3 ms·

In your database, you should always use types with time zones.

by aib 6y ago

eVeechu7 6y ago
Or Unix time.
dmlittle 6y ago
Not sure about all different databases but Postgres, for example, always stores datetimes in UTC and converts them back to a display timezone when outputting the values[0]. > For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. [0] https://www.postgresql.org/docs/current/datatype-datetime.html https://www.postgresql.org/docs/current/datatype-datetime.ht...
- raziel2p 6y ago
  You're assuming that the "timestamp with time zone" type is always used. I've worked on an application where it was assumed/required that UTC was configured on the server level, so regular timestamps were used. Didn't agree with that at all, but it happens.
AmericanChopper 6y ago
Time stamp + TZ is a useful feature if you want to store TZ along with the time stamp. But I think that’s nearly always a bad approach. The only part of your system that needs the time stamp converted into a TZ is your human user. Performing that conversion on the presentation layer will almost always lead to less complexity.
- unilynx 6y ago
  The most common situation I know of where you store the time with its original TZ is when dealing with recurring times (opening/closing items, appointments), as you need to follow the original DST/summertime the user was in. Which also requires you to store the TZ location (ie city) and not just the name or offset of the timezone itself.
  - AmericanChopper 6y ago
    Yeah, I meant to put a “usually” qualifier in that previous comment. Rostering has the same problem. Event scheduling can also run into similar problems. The best solution really depends on a lot of context specific details, but most of the better solutions I’ve personally seen have put TZ in a seperate field, rather than using TZ-typed timestamps, and then use the TZ when required instead of as the default storage format.
- rocqua 6y ago
  For recording when something happend, that makes some sense. Though if you want to report the day of an event, it is still neccesary to include a timezone. For planned events, it becomes really hard to determine what to do when changing locale.

In your database, you should always use types with time zones.

by aib 6y ago

In your database, you should always use types with time zones.

eVeechu7 6y ago

Or Unix time.

dmlittle 6y ago

Not sure about all different databases but Postgres, for example, always stores datetimes in UTC and converts them back to a display timezone when outputting the values[0]. > For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. [0] https://www.postgresql.org/docs/current/datatype-datetime.html https://www.postgresql.org/docs/current/datatype-datetime.ht...

raziel2p 6y ago
You're assuming that the "timestamp with time zone" type is always used. I've worked on an application where it was assumed/required that UTC was configured on the server level, so regular timestamps were used. Didn't agree with that at all, but it happens.

AmericanChopper 6y ago

Time stamp + TZ is a useful feature if you want to store TZ along with the time stamp. But I think that’s nearly always a bad approach. The only part of your system that needs the time stamp converted into a TZ is your human user. Performing that conversion on the presentation layer will almost always lead to less complexity.

unilynx 6y ago
The most common situation I know of where you store the time with its original TZ is when dealing with recurring times (opening/closing items, appointments), as you need to follow the original DST/summertime the user was in. Which also requires you to store the TZ location (ie city) and not just the name or offset of the timezone itself.
- AmericanChopper 6y ago
  Yeah, I meant to put a “usually” qualifier in that previous comment. Rostering has the same problem. Event scheduling can also run into similar problems. The best solution really depends on a lot of context specific details, but most of the better solutions I’ve personally seen have put TZ in a seperate field, rather than using TZ-typed timestamps, and then use the TZ when required instead of as the default storage format.
rocqua 6y ago
For recording when something happend, that makes some sense. Though if you want to report the day of an event, it is still neccesary to include a timezone. For planned events, it becomes really hard to determine what to do when changing locale.