Hacker News Reader

Comment by aib | Hacker News Reader

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

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.

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.

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.

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.

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.ht...

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.

Or Unix time.

Reply on news.ycombinator.com