22 ms·

So the article seems to imply you should store all timestamps as UTC (with an additional timezone string ID). But for events in the future that needs to happen

• draw_down 8y ago
  Sure... except, what is "future"? Or rather, how do we know which things are in the future?
• cryptonector 8y ago
  Yes, you have to periodically recompute times. For example, if you store time in TAI then you'll have to recompute all other future times as leap seconds are announced.
  • hanikesn 8y ago
    I don't think I ever heard of anyone (besides scientists) to store timestamps in TAI. Though Ada supports leap second calculations in the standard library: http://www.adaic.org/resources/add_content/standards/05rat/html/Rat-7-3.html http://www.adaic.org/resources/add_content/standards/05rat/h...
    • cryptonector 8y ago
      Dealing with time well into the future is rough because of the unknown timezone changes and unknown leap seconds. There's not much you can do. Computing future times in TAI from user inputs in wall clock time is fraught, so I guess you can't store TAI in those cases. For a calendar app, since users want to deal in wall clock time, you have to store time with timezone and with leap seconds (so UTC + time zone). And you have to store timezone, not offset to UTC -- timezones can change. At least internally, however, dealing in TAI can be helpful[0]. Also, IIRC there's a proposal to redefine UTC as without leap seconds[1]... That scares me. Users really need wall clock time. The continued existence of TAI was questioned in a 2007 letter from the BIPM to the ITU-R which stated "In the case of a redefinition of UTC without leap seconds, the CCTF would consider discussing the possibility of suppressing TAI, as it would remain parallel to the continuous UTC."[16] [0] ttps://cr.yp.to/time.html [1] https://en.wikipedia.org/wiki/International_Atomic_Time https://en.wikipedia.org/wiki/International_Atomic_Time
      • tialaramex 8y ago
        "That scares me. Users really need wall clock time." Why would getting rid of leap seconds from UTC be a problem for that? The reason there are leap seconds is nothing to do with wall clock time, it's because some people feel the time ought to be intimately connected to the Earth's rotation, but the Earth doesn't oblige by rotating steadily. In my view the people demanding this relationship be maintained ought to take responsibility for fixing it from their side. Speed up or slow down the Earth. Can't? Too bad then, but don't expect us to keep fiddling with the clocks.
        • perl4ever 8y ago
          I don't understand why you say leap seconds have nothing to do with wall clock time. Don't wall clocks include leap seconds?
          • tialaramex 8y ago
            Wall clocks just track UTC. Today they have leap seconds because we defined UTC to include leap seconds, tomorrow if UTC or a replacement universal time no longer has leap seconds then wall clocks won't have leap seconds.
            • cryptonector 8y ago
              But then people will notice a few decades later that noon in UTC-minus-leap-seconds is not noon.
              • tialaramex 8y ago
                But it _is_ noon. You are probably thinking of sun transit time, "solar noon", the moment in each day when the sun appears to be "highest" in the sky. This varies of course by position on the Earth, it's how people set "noon" when they didn't need to agree with anybody more than a horse ride's distance away what the time was. So, let's say at least 200 years or more ago. Do you know when solar noon is where you live now? No? Because it's irrelevant. Huge numbers of people live in places where the solar noon changes by an entire hour twice a year for no sensible reason. Does this cause a huge problem? No, there's a slightly elevated rate of road accidents and things like that, but nothing major. A few seconds per decade is _nothing_.
  • Animats 8y ago
    There's "Google time", their approach to leap seconds. 12 hours before a leap second, Google's version of UTC time starts running slightly slow, so that it loses a second over the next 24 hours.[1] Some Google database systems rely on tight time synchronization to order update events, so they had to have a global monotonic clock. [1] https://developers.google.com/time/smear https://developers.google.com/time/smear
• bad_user 8y ago
  Actually the meaning of a date like: 2018-05-26T13:45:21+02:00 Will never change, regardless how many time zone changes you may have. What does change is the method to compute the time difference. So can you say how many days, hours, minutes, seconds ago that was from: 2043-02-12T11:15:16+02:00 You won’t be able to, because then you have to account for leap seconds, etc. Also “next Tuesday” does have to take into account the time zone as you may need to deal with daylight saving time. However “next Tuesday” is not a date-time, but a relative difference from where you are now and by pre-calculating you lose information. Also relevant, in such cases storing the time zone doesn’t help because the user himself might travel and next Tuesday at 7:00 can remain the same, no matter what time zone you’re talking about.
  • 0x0 8y ago
    It depends. It could be a meeting in a particular city at 4pm local time. A "+02:00" or "-05:00" specifier is insufficient because the government of the particular city/country might change the time zone rules between now and when the event is to occur. So you really need to store "2018-08-01 16:00:00 America/Argentina/Buenos Aires" for example, because maybe after 2018-06-01 they decided to go for "-05:30".
    • lomnakkus 8y ago
      Indeed. There's a concept of relative physical time a) "I'll be there in 12 hours", implication being that if there happens to be a DST change, it doesn't matter. and relative calendar time b) "I'll meet you 15:00 next monday". Both of which are valid. What's interesting is: which of these is most useful in programming, generally? Which should the APIs make easier, or even cater for at all? I would submit that most applications only care about "physical time"[1], but specifically [b]logging[/b] is actually really interested in calendar time. Fortunately, with logging there's so much volume that you can usually tell pretty easily when there's been a discontinuous time event -- it's a pain in the ass to rebuild timestamps, post hoc, though. [1] Calender-type application is actually pretty niche IME. Opinion may vary.
  • wlesieutre 8y ago
    The meaning of “2018-05-26T13:45:21+02:00” may not change, but if 2018-05-26 rolls around and the restaurant where you have a lunch reservation at 13:45 local time is no longer in the +02:00 time zone, it’s not the same time that your reservation is scheduled for anymore.
• mjevans 8y ago
  Time is difficult and system design depends on the intended consumer. For the case you propose, I read it as a "meeting" planned at a point in the future. In this case storing the start of the meeting as a bare timestamp is inappropriate design. What really should be encoded is the set of rules for identifying the proper timestamp. There was an existing standard mentioned for that which I'd use as a starting point if designing or implementing (if I chose their design) such a system.
  • afpx 8y ago
    Thank you. There seems to be some serious overdesign in the original article and a many of the follow up comments. Design should follow specific classes of user’s goals and intent, not an attempt to craft a universal solution for everyone, across all time. Form follows function. If the concept of “10 AM on Saturday” is important to your users, model the software in line with that domain knowledge. All problems are bounded by context, and knowing the boundaries makes designs simplier and easier to manage.
• Terr_ 8y ago
  IMO the biggest problem isn't the how you store information or even do the date-math. The most frustrating thing is drawing out user-intent, distinguishing between two use-cases which are so similar that most users won't even know what they want. Specifically, future events which aren't tied to a geographical location, and ones which are. And in the latter case, determining which single location it should be pegged to. A: "We'll have a global phone call to discuss this crisis in exactly X hours from now." B: "The keynote for the convention in CityName will occur at 3pm."
  • laumars 8y ago
    Exactly that. It's like when people ask the difference between crond and atd, one fires events at a defined date/time in the future and the other fires events at a count of date/time from the present.
  • senlutomchen 8y ago
    Another example of confusing user-intent is "last X days". I.e Does "Last 2 days" indicate: A. Last 48 hours? B. Yesterday + Today up to now C. Yesterday + the day before.
    • cryptonector 8y ago
      "One month from now" is horrible. Is that "30 days from now" or "same day number next month"? If the latter, then how do you handle a 31st when the next month has 30 days or fewer? (Or a 29th when the next month is February and this is not a leap year?) This is a difficult subject. Recurrence is especially tough to model in a database.
      • narag 8y ago
        It depends on the context. I've programmed date logic for holiday calculation, days open for business and deadlines. I like working with dates because it's always well specified.
      • wafflebear 8y ago
• gsich 8y ago
  There is a solution for this, Unix timestamps.
  • _wmd 8y ago
    Looks like someone didn't read the article :) If you normalize to UTC for some future date with a timezone in Lower Left Elbonia Standard Time (GMT+7:42), and the Elbonian Revolutionary Army successfully form an overnight junta, retroactively declaring the country's timezone to be Free Peoples' Elbonia Revolutionary Time (GMT-12:00), suddenly your DB rows make no sense whatsoever. The absolute point in time relied on future knowledge that did not yet exist, that invalidated any previous estimate of when it would occur. An external timezone reference (the Olsen DB) must be continually updated and separately referenced
  • XR0CSWV3h3kZWg 8y ago
    That fails for the case that you have an appointment at 3pm in 30 days, and 15 days later the timezone's offset changed.
    • gsich 8y ago
      How does that fail? I have an integer for an appointment. I am at that appointment at that given integer. This applies for all timezones. If the timezone changes, the integer does not. What you are saying is that the timezone changes and the integer changes aswell, but then it's not the same appointment anymore.
      • wtetzner 8y ago
        From the user's perspective it is the same appointment. They think they have an appointment at 3:00 PM on Thursday. If there's a timezone change, and you just stored an integer, then from the user's perspective the meeting changed from 3:00 PM to 2:00 PM. Of course, if you have a meeting that spans timezones, this is unavoidable. I think the real solution actually lies in how the meeting time is expressed to the user so they don't have misconceptions about it.
      • deleted 8y ago
• derefr 8y ago
  Depends. Some things need to happen N seconds from now. Other things (usually things involving humans) need to happen on a day that those humans will look at their calendars and say "oh, it's [that day]! Better do [that thing]!"
  • jgtrosh 8y ago
    The comment above was referring to the second kind, but it's true that it englobed too much
• leonroy 8y ago
  We spent weeks on this for our new conference calling app to determine when a user says "Setup a call at 10am for my group every week" that come October 29th 2018 the call takes place at 10am, not 9am following a DST change. After a heckuva lotta research and reading we determined that we needed to store the scheduled meeting time using two values; the local datetime and the desired timezone eg. scheduledAt: 2018-05-29T23:41:16.167 scheduledAtZone: Europe/London Everything else in the system like created, updated, ended data is stored using UTC and transposed on the client using the user's stored time zone preference. eg. createdAt: 2018-05-29T22:41:16.167Z The application server your app is running on of course needs to run on Etc/UTC but with that in place (so far...touch wood) we haven't had a problem in our usage.
  • 0x0 8y ago
    Yep, that's my conclusion too: storing timestamps for events in the past and up to present time - unix timestamp with optional tzid, and re-calculate local time formatting from scratch for display every time. Events in the future, do the opposite: store as yyyymmddhhiiss + tzid, and re-calculate utc from scratch for calculations every time. As a rule of thumb, at least.
    • ithkuil 8y ago
      Interesting. And at some point the future becomes the past and you have to switch the representation and freeze it. Not trivial.
  • cryptonector 8y ago
    You also need to periodically compute the next event, but you can't do it too far in advance. You have to do it close enough to the event that that you can adjust to timezone definition changes. Hopefully you don't have to think about leap seconds.
    • acqq 8y ago
      Why would anybody schedule events in local time to happen on leap seconds?
• mikekchar 8y ago
  When we were building a golf tee time web site we eventually decided on this as well: always store the date-time as a string in the timezone local to the event. Convert to a date-time object in whatever timezone the user prefers (or golf tee time service APIs prefer :-P) on demand. There is still a caveat, though. Locations can change timezone. Sometimes they do it fairly often (like every few years). You need access to a good timezone/location database that is updated regularly :-( When we were doing this project, I was really surprised at how often we got problems. If you sell things all over the world (there are golf courses in ridiculous places), the probability that you will stumble into an anomaly is surprisingly high. Alas, this project was not a commercial success as the margins on tee times are quite small (and the tee time services you have to work with are often quite badly written). But it was quite fun to work on.
  • stouset 8y ago
    So to be absolutely correct, you store future timestamps in localtime, along with GPS coordinates of the location for the event. Then you determine the time zone of the physical locale based on a GIS lookup. Then when rendering, you offset that against the timezone of the user’s current location. I think. I hate time.
    • mikekchar 8y ago
      Yep. That's exactly what we did :-) For the short time we were running the service (about a year or so) it worked great ;-)
• Iv 8y ago
  > "wall clock point in time" The tricky thing to realize is that this is not a datetime. This is more of a contract/condition saying "when localtime will be at this value" or better yet "when localtime will have exceeded this value for the first time" because you know someone will put a date on a leap second or missing DST hour at one point.
  • niftich 8y ago
    All of calendaring is essentially conditions, though. Your point about this being a separate concept still stands. It's unfortunate how few libraries exist to handle abstract datetime constructs, and how frequently programming environments screw this up. The only one I know of that doesn't mince concepts is Java 8 Time [1], where points-on-timeline timestamps are clearly separate from human-centric calendrical concepts like Time-of-Day (LocalTime) and Month-Day without a year, so there's an appropriate datatype to model lots of common use-cases of date math. [1] https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html https://docs.oracle.com/javase/8/docs/api/java/time/package-...
  • Spooky23 8y ago
    It depends on the application and what the customer need is. My son’s friend in school was born on February 29. We could go through the time pedantry and claim that she is 1 year old (she is 6), or pick an arbritrary moment in time (say March 1, except for leap year) and get on with the party. If you’re plotting a satellite course, the details matter, but many, if not most use cases require consistency over precision.
    • Iv 8y ago
      If you are dealing with physics, like a satellite course, "wall time clock" is irrelevant. Wall time clock is a human construct that is ambiguous in many edge cases, dependent on position and local politics. Indeed, your son's friend's age is an ambiguous notion, Hence the need of a more precise contract than a simple date.
• Neeek 8y ago
  I might not be understanding your scenario correctly, but isn't it stated that UTC has 00:00 offset and should be stored that way? You would then add an offset based on current tzdata for that timezone string which would make it dynamic and accurate for the situation you described.
  • sudhirj 8y ago
    There's two distinct things at play here - storing times in the past and present, and times in the future. For the past and present, UTC is fine, and a source timezone if necessary. To track the exact instant when something happened to me, just storing UTC is fine. If I want to know what where the hands on my clock were when that happened, you also store which clock I was using separately (timezone), but this is optional and doesn't prevent accuracy. For future times, relating to humans, like on a calendar, there's a very important distinction - I want to set appointment when my clock shows a certain time, irrespective of what the timezone rules (Daylight saving time rules can change) are at that point. Here, storing the UTC time for my appointment according to today's rules is a problem - you must simply store what I expect to see on the clock and apply the timezone rules lazily at the last possible minute.
• erpellan 8y ago
  It gets even more fun if you need to answer questions like: "When this event was scheduled 6 months ago for today at 2pm, what UTC time did that represent then vs now?". Timezone rules also change. So when you're dealing with past and future you might also need the timestamp of the timestamp!
  • narag 8y ago
    Exactly, storing both the universal time and the legal local time.
• brongondwana 8y ago
  That's what we do in the Cyrus IMAP scheduler for future alarms. We set the trigger at most 30 days in the future, and at that point it will re-calculate when the alarm should fire. Using the tz database at the time each time around. Of course, calendar events in icalendar have the tzdata stored along with them, in theory, so they should never change unless your client updates them. Which leads to its own world of fun.
• nwah1 8y ago
  If you had UTC and the original timezone code, you could run a job that updates future timestamps according to timezone changes. This might keep things cleaner.
• zamalek 8y ago
  Some timezones change twice a year. Seriously![1] A fellow engineer was responsible for the web front-end framework for dealing with time (back when we built it all here) and he spent months working through some of the strangest crap, some JS, some wierd crap humans dream up. Good example: you, user A, are in a TZ that switches biannually - at one point in the year you lose an hour, at another you experience an hour twice. Now, another user B is also in one of these whacko places and creates a meeting (while experiencing an hour for the second time) or what-have-you for a future time where user A goes through their local groundhog hour. You have a backend server that, for legacy reasons, simply truncates TZ and goes with its own local TZ. That server is in another TZ that has an identity crisis. JS is doing dumb stuff that only JS could do. Does your brain hurt yet? I'm pretty sure that this engineer can now perceive four dimensions. He didn't receive a bug report until we migrated to UTC. UTC made things worse, somehow. As it turns out UTC is only good for when you care about a machine doing something at some time, or when working relationally. Store TZ (ideally location, an offset is not a TZ) along your dates. When presenting them include that information, as well as relational ("3 days, 4 hours ago at 00h15 in WA"). Time is nuts. [1]: https://en.wikipedia.org/wiki/Daylight_saving_time https://en.wikipedia.org/wiki/Daylight_saving_time
  • petre 8y ago
    > "3 days, 4 hours ago at 00h15 in WA" This sounds exactly like a line out of some Douglas Adams book. Which is perfectly fine as long as no one booking meetings on that app has read any of his books.