5 ms·

They and everybody else should avoid using timestamp representations for any dates so far in the future. Because, it is a nightmare to translate timestamps when

They and everybody else should avoid using timestamp representations for any dates so far in the future. Because, it is a nightmare to translate timestamps when the timezone database is updated. Which you would have to do, if e.g. the EU finally manages to get rid of summer time and you care that dates past that change stay the same.

mindwok 5y ago
What’s the alternative to a timestamp representation?
- wyufro 5y ago
  A string, ISO 8601, https://en.wikipedia.org/wiki/ISO_8601 https://en.wikipedia.org/wiki/ISO_8601 Preferably RFC3339 (which is a specific profile of ISO 8601) but, as noted in a sibling comment, it isn't always appropriate for future dates.
aulin 5y ago
wait, why? unix timestamps are safe from this kind of mess as they are UTC seconds since the epoch, all the tz-aware conversion happens when you need to display localized dates but the timestamp you save should be as neutral and tz-unaware as possibile. I've seen way more bugs from people assuming system clock to be UTC while it was localized. Like missing and overwritten data on daylight saving changes.
- davrosthedalek 5y ago
  The problem is that the contract normally doesn't specify epoch seconds. So if the translation of UTC since epoch to local time changes, the local time specified in the contract does not, so your UTC since epoch timestamp has to.
- CorrectHorseBat 5y ago
  I don't think so. Imagine you have date saved somewhere at 10am 1 July 2031. If the EU abolishes DST in between, do you want the hour changed to 9am or keep it at 10am? I'd say both could be correct.
  - aulin 5y ago
    You convert it to the local time at that location at that point in history. That's something that belongs to time localization, timestamp should be universal and immutable against those changes.
    - CorrectHorseBat 5y ago
      So I'd say you are arguing against using timestamp representations for any dates far in the future since they can change depending on what happens with timezones and DST, but timestamps cannot.
      - aulin 5y ago
        My point is that either you save a properly localized tz-aware time in unambiguous standard format (iso 8601?) or you save a universal timestamp (and location if needed) and defer all the localization process to the moment you need to display localized time. From my experience the latter is safer because people tend to save datetime strings in a format that's either not standard nor unambiguous.
        xxpor 5y ago
        I don't think you're answering the question here though. It's completely plausible there are situations where the contant is local time, not UTC (or TAI, if you want to get really obnoxious). Let's say I made an appointment to get my hair done in Berlin 10 years in advance, at 14:00 local time 29 Dec 2031, for whatever reason. Let's say someone goes crazy and moves Berlin to +1:30 this decade. I would still expect to show up at 14:00 local, but that's not the same universal time it was 10 years ago. How do you represent that in your DB where everything is in UTC?
        deleted 5y ago
        [deleted]
        aulin 5y ago
- dtech 5y ago
  You lose context. Say you have 2100-01-01T10:00:00 in PST. That's 4102452000, so you store it. A few decades later, timezones are changed. PST should add 30 minutes, all others stay the same. You only have 4102452000, what should you convert it into? You lost the time zone information, so you can't make the correct decision.
  - oleganza 5y ago
    Your example is valid and replies are ill-informed. There is a difference between a "date" and "time interval". Timestamps are good for storing intervals, but if you need to plan something on a _date_, you need to store the date. This means you care not about specific number of seconds having passed since now, but about what calendar (and the clock) is saying when the event has to happen. Example: nobody cares if a concert planned on November 19, 2024 at 19:00 in Honolulu starts in 12345600 seconds or 12349200 seconds since now. But everyone cares that everone's calendars and watches are in sync by that time and show specifically that date and that time, regardless of how many times people switched DST or timezones in the years in-between.
  - aulin 5y ago
    if you need context, e.g. location, you can still save it in another field and you will be able to correctly display localized time at any point given the universal timestamp and the current location. Or you can save properly localized and tz-aware time, I just find timestamps safer from pitfalls.
    - dtech 5y ago
      It's not about localization, it's about correctness. you need both the timestamp and tz if you care about the future local date and time.
      - aastronaut 5y ago
        You seem to make a contradicting statement here with what you wrote in the sibling comment, no? timezone information are influenced by political means, whereas localization (lat/long) information is not. UTC timestamp + localization seem more correct to me.
- dzdt 5y ago
  For many purposes the correct specification of a future time is in terms of local time at a particular location on that date. This is true for financial contracts (including trillions of dollars in options markets -- 10am in New York means NY time) but also of work schedules (scheduled to arrive at 9am? That is on the workplace clock, not UTC), local transit schedules, and many other things. That means when daylight savings time rules change or timezone lines are moved the UTC time of these events change. Unfortunately I don't believe anyone has standardized a format for storing times which are specified local time at a specified location on a specified date. So it is all roll-your-own or use UTC or zoned datetime and be burned when things change.

mindwok 5y ago

What’s the alternative to a timestamp representation?

wyufro 5y ago
A string, ISO 8601, https://en.wikipedia.org/wiki/ISO_8601 https://en.wikipedia.org/wiki/ISO_8601 Preferably RFC3339 (which is a specific profile of ISO 8601) but, as noted in a sibling comment, it isn't always appropriate for future dates.

aulin 5y ago

wait, why? unix timestamps are safe from this kind of mess as they are UTC seconds since the epoch, all the tz-aware conversion happens when you need to display localized dates but the timestamp you save should be as neutral and tz-unaware as possibile. I've seen way more bugs from people assuming system clock to be UTC while it was localized. Like missing and overwritten data on daylight saving changes.

davrosthedalek 5y ago
The problem is that the contract normally doesn't specify epoch seconds. So if the translation of UTC since epoch to local time changes, the local time specified in the contract does not, so your UTC since epoch timestamp has to.
CorrectHorseBat 5y ago
I don't think so. Imagine you have date saved somewhere at 10am 1 July 2031. If the EU abolishes DST in between, do you want the hour changed to 9am or keep it at 10am? I'd say both could be correct.
- aulin 5y ago
  You convert it to the local time at that location at that point in history. That's something that belongs to time localization, timestamp should be universal and immutable against those changes.
  - CorrectHorseBat 5y ago
    So I'd say you are arguing against using timestamp representations for any dates far in the future since they can change depending on what happens with timezones and DST, but timestamps cannot.
    - aulin 5y ago
      My point is that either you save a properly localized tz-aware time in unambiguous standard format (iso 8601?) or you save a universal timestamp (and location if needed) and defer all the localization process to the moment you need to display localized time. From my experience the latter is safer because people tend to save datetime strings in a format that's either not standard nor unambiguous.
      - xxpor 5y ago
        I don't think you're answering the question here though. It's completely plausible there are situations where the contant is local time, not UTC (or TAI, if you want to get really obnoxious). Let's say I made an appointment to get my hair done in Berlin 10 years in advance, at 14:00 local time 29 Dec 2031, for whatever reason. Let's say someone goes crazy and moves Berlin to +1:30 this decade. I would still expect to show up at 14:00 local, but that's not the same universal time it was 10 years ago. How do you represent that in your DB where everything is in UTC?
        deleted 5y ago
        [deleted]
        aulin 5y ago
dtech 5y ago
You lose context. Say you have 2100-01-01T10:00:00 in PST. That's 4102452000, so you store it. A few decades later, timezones are changed. PST should add 30 minutes, all others stay the same. You only have 4102452000, what should you convert it into? You lost the time zone information, so you can't make the correct decision.
- oleganza 5y ago
  Your example is valid and replies are ill-informed. There is a difference between a "date" and "time interval". Timestamps are good for storing intervals, but if you need to plan something on a _date_, you need to store the date. This means you care not about specific number of seconds having passed since now, but about what calendar (and the clock) is saying when the event has to happen. Example: nobody cares if a concert planned on November 19, 2024 at 19:00 in Honolulu starts in 12345600 seconds or 12349200 seconds since now. But everyone cares that everone's calendars and watches are in sync by that time and show specifically that date and that time, regardless of how many times people switched DST or timezones in the years in-between.
- aulin 5y ago
  if you need context, e.g. location, you can still save it in another field and you will be able to correctly display localized time at any point given the universal timestamp and the current location. Or you can save properly localized and tz-aware time, I just find timestamps safer from pitfalls.
  - dtech 5y ago
    It's not about localization, it's about correctness. you need both the timestamp and tz if you care about the future local date and time.
    - aastronaut 5y ago
      You seem to make a contradicting statement here with what you wrote in the sibling comment, no? timezone information are influenced by political means, whereas localization (lat/long) information is not. UTC timestamp + localization seem more correct to me.
dzdt 5y ago
For many purposes the correct specification of a future time is in terms of local time at a particular location on that date. This is true for financial contracts (including trillions of dollars in options markets -- 10am in New York means NY time) but also of work schedules (scheduled to arrive at 9am? That is on the workplace clock, not UTC), local transit schedules, and many other things. That means when daylight savings time rules change or timezone lines are moved the UTC time of these events change. Unfortunately I don't believe anyone has standardized a format for storing times which are specified local time at a specified location on a specified date. So it is all roll-your-own or use UTC or zoned datetime and be burned when things change.