4 ms·

I would contend that you shouldn't store anything but current unix timestamps in UTC in your database. If you must store time in some other way, then the two co

• merb 4mo ago
  In that case only storing utc did not work when you created a date in the future before you updated tzdata
  • jedberg 4mo ago
    I edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
    • phantom784 4mo ago
      How would this solve the British Columbia issue as described in the article?
    • eduction 4mo ago
      Your time library is not going to design your database schema for you.
• ppchain 4mo ago
  The issue described in the post is an example of when you cannot just rely on Unix timestamps. Specifically it comes down to which date is authoritative. A appointment with your dentist at 2pm Pacific Time in December 2026 has changed Unix timestamps in British Columbia. The dentist doesn't care about the Unix timestamp, she cares about the wall clock local time when you arrive for the appointment.
  • jedberg 4mo ago
    I edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
    • ncruces 4mo ago
      The post is for the otherworldly magician who wrote your library then.
  • ngaheer 4mo ago
    "Which date is authoritative". I don't understand this. The consumer books in his/ her local time stamp i.e. 12 PM pacific. Gets stored as Epoch milliseconds (and is passed around as a data structure i.e. Date struct with UTC as the timezone) and the providers sees the time stamp 3 PM EST or 2 PM CST depending on it's timezone at runtime (interface the provider it works with). I don't understand why a specific timezone has to be "authoritative" here. What am I missing.
    • mgaunard 4mo ago
      Never heard of DST? The authoritative time is constant in the local time zone, but needs to change in UTC twice a year. This is the exact reason people store time in local time zones. Also remember the date/time where DST switching occurs is entirely timezone-specific, and it's not necessarily the same pattern every year (as demonstrated with British Columbia).
      • Insimwytim 4mo ago
        DST works fine with Unix timestamps. It's the scheduling changes that disrupt DST switch (or something else) need adjusting to. But this is usually planned in advance and everyone would (or at least should...) update their tzdata. The amount of issues you'll have due to those (comparatively rare) changes cannot even begin to compare to the amount issues you'll have with datetime stored in timezones.
• rini17 4mo ago
  If you don't understand what the library is doing, and blindly put in local time without any consideration, you will get bitten someday. And all libraries use the same timezone database/logic anyway and run into same issues the author describes.
• ivan_gammel 4mo ago
  It‘s a common mistake to store everything as UTC timestamps and shows lack of understanding of time domain. Local time exists and it is neither UTC or timezone-dependent. Doctor office opens at 8 a.m. regardless of whether it is DST or not. Appointments are made in local time. Store them in local time.
  • remus 4mo ago
    > Appointments are made in local time. Store them in local time. You may just be illustrating a particular use case, but it is more ambiguous in the general case. For example if you have arranged a meeting with someone in another timezone then maintaing the local timezone could lead to a misalignment for one of the participants.
    • ivan_gammel 4mo ago
      This is ok. When future local user time offset is unpredictable, pinning meeting time to a certain location is a good strategy. If at some point meeting shifts for some user, well-designed calendar app could warn them and let reschedule. It still works better than pure UTC because it is predictable for at least some of the users.