4 ms·

But you frequently need to store the original timezone, to be able to do date calculations after storing it. For instance, a user added an appointment and her

by wulczer 15y ago

But you frequently need to store the original timezone, to be able to do date calculations after storing it. For instance, a user added an appointment and her timezone is America/Los_Angeles. Then she moved to China and changed her timezone in your app to Asia/Shanghai. Then you run analytics to see how many users had had appointments on weekdays. When storing date information in persistent stores (like databases), it's always advisable to store the time and the timezone.

the_mitsuhiko 15y ago
Even in that situation it's a terrible idea to store local time alone. In the calendar situation (which is the exception not the rule!) I personally would store the time in UTC, in local time and the name of the country separated. I would not store the local time alone because what do you show in the calendar if you are in a different country? If the assumption is that the problem is a theoretical one because nobody has appointments in the middle of the night when DST changes come, I present you with sysadmins, rescue workers and other people that do have these things scheduled at odd times. I know that around DST changes many people explicitly not work over night to avoid these issues. Same with year changes and other situations where computing and time does not work properly. For instance overnight trains in Europe just wait for an hour and do nothing.
- wulczer 15y ago
  You store the local time and timezone. You show the value in whatever timezone the user configured the application. If you store only Unix time, you don't have enough information. If you store both the Unix timestamp, the local time and the timezone, you have too much information. You can derive Unix time from local time and the timezone. Ergo: store time with time zone.
  - DannoHung 15y ago
    Why not store the Unix timestamp and the timezone that was set when created? Then you can just as easily determine what the original local time was and also convert to whatever the current local time is. And you save yourself the misery of endless conversion when trying to compare anything internally.
  - the_mitsuhiko 15y ago
    > You store the local time and timezone. You show the value in whatever timezone the user configured the application. Because local time is ambiguous. During DST changes local time can mean that it happens twice. Your calendar would have to show 4 AM twice for instance and if you want to make an appointment there you would have to select which one you actually mean, thus UTC + local time. But granted, it does not happen often that people schedule appointments during DST switches, but certain professions have to so certain people already have to live with that. > If you store both the Unix timestamp, the local time and the timezone, you have too much information. Nope. That is incorrect. If I store local time only + timezone and someone sets a date during that transition window it's ambiguous. If I store the date in UTC only and remember the timezone that date was intended for I will not be able to account for changes in the timezone. For instance the legislation of the country could deactivate or change DST settings (which is not uncommon). In that case the timezone information of my operating system will change again and my appointment will move without my consent. If I store both I can see if they are still in sync and if not, prefer the local time and rebase as necessary.
    - wulczer 15y ago
      Oh, I see what you're referring to. Yes, storing the local time and the timezone is not exactly what's enough, all that time I was thinking about the PostgreSQL timestamp with time zone type, which is preferable to use vs storing the Unix timestamp. I guess we've been talking past each other, with me being the main culpable :)
fanf2 15y ago
Local time + timezone is not enough, because timezones change. For time in the past, including measurements of the current time, store something equivalent to UTC, e.g. time_t or ISO 8601. For times in the future, store local time and location, and do the location -> timezone lookup at rum time. Add an earlier/later disambiguation bit to deal with DST transitions and other ambiguities. Sadly no-one does it this way which is why you have problems like needing to fix all the copies of the TZ information in your Exchange server when TZ rules change. Because of this, the location to TZ database is an exercise for the reader... http://fanf.livejournal.com/104586.html http://fanf.livejournal.com/104586.html

by wulczer 15y ago

the_mitsuhiko 15y ago

Even in that situation it's a terrible idea to store local time alone. In the calendar situation (which is the exception not the rule!) I personally would store the time in UTC, in local time and the name of the country separated. I would not store the local time alone because what do you show in the calendar if you are in a different country? If the assumption is that the problem is a theoretical one because nobody has appointments in the middle of the night when DST changes come, I present you with sysadmins, rescue workers and other people that do have these things scheduled at odd times. I know that around DST changes many people explicitly not work over night to avoid these issues. Same with year changes and other situations where computing and time does not work properly. For instance overnight trains in Europe just wait for an hour and do nothing.

wulczer 15y ago
You store the local time and timezone. You show the value in whatever timezone the user configured the application. If you store only Unix time, you don't have enough information. If you store both the Unix timestamp, the local time and the timezone, you have too much information. You can derive Unix time from local time and the timezone. Ergo: store time with time zone.
- DannoHung 15y ago
  Why not store the Unix timestamp and the timezone that was set when created? Then you can just as easily determine what the original local time was and also convert to whatever the current local time is. And you save yourself the misery of endless conversion when trying to compare anything internally.
- the_mitsuhiko 15y ago
  > You store the local time and timezone. You show the value in whatever timezone the user configured the application. Because local time is ambiguous. During DST changes local time can mean that it happens twice. Your calendar would have to show 4 AM twice for instance and if you want to make an appointment there you would have to select which one you actually mean, thus UTC + local time. But granted, it does not happen often that people schedule appointments during DST switches, but certain professions have to so certain people already have to live with that. > If you store both the Unix timestamp, the local time and the timezone, you have too much information. Nope. That is incorrect. If I store local time only + timezone and someone sets a date during that transition window it's ambiguous. If I store the date in UTC only and remember the timezone that date was intended for I will not be able to account for changes in the timezone. For instance the legislation of the country could deactivate or change DST settings (which is not uncommon). In that case the timezone information of my operating system will change again and my appointment will move without my consent. If I store both I can see if they are still in sync and if not, prefer the local time and rebase as necessary.
  - wulczer 15y ago
    Oh, I see what you're referring to. Yes, storing the local time and the timezone is not exactly what's enough, all that time I was thinking about the PostgreSQL timestamp with time zone type, which is preferable to use vs storing the Unix timestamp. I guess we've been talking past each other, with me being the main culpable :)

fanf2 15y ago

Local time + timezone is not enough, because timezones change. For time in the past, including measurements of the current time, store something equivalent to UTC, e.g. time_t or ISO 8601. For times in the future, store local time and location, and do the location -> timezone lookup at rum time. Add an earlier/later disambiguation bit to deal with DST transitions and other ambiguities. Sadly no-one does it this way which is why you have problems like needing to fix all the copies of the TZ information in your Exchange server when TZ rules change. Because of this, the location to TZ database is an exercise for the reader... http://fanf.livejournal.com/104586.html http://fanf.livejournal.com/104586.html