16 ms·

Storing UTC is not a silver bullet (2019)

243 points by nickkell 5y ago

donalhunt 5y ago
This reminds me that Atlassian still doesn't support per-user date time formatting for some of their products (I use iso8601 where possible to avoid confusion in multinational settings). ಠ︵ಠ
samwillis 5y ago
I think summary here is that if you are storing an exact moment in time then UTC does the job. If however you are storing a future date and time in a certain location, due to the uncertainty about future daylight saving time changes you have to save it with time zone information in order to adjust for future changes to that system. I suppose it’s the difference between exact time and social/political time (there is probably a better term for that).
- em3rgent0rdr 5y ago
  > "social/political time (there is probably a better term for that)." The better term is "civil time": https://en.wikipedia.org/wiki/Civil_time https://en.wikipedia.org/wiki/Civil_time
- masklinn 5y ago
  > due to the uncertainty about future daylight saving time changes It’s not just DST, it’s any TZ-related changes: locations can change TZ for other reasons than dst. > I suppose it’s the difference between exact time and social/political time. There’s really no such thing as exact time. The closest is UT1, and it’s not really practical for normal applications.
  - samwillis 5y ago
    > It’s not just DST, it’s any TZ-related changes: locations can change TZ fir other reasons than dst. Exactly, and for that time zone info will not be enough, you would need geographic coordinates.
    - deleted 5y ago
      [deleted]
    - spacemanmatt 5y ago
      To be more accurate you should also store relative velocity so the Lorentz transformation can be applied at display time
    - scatters 5y ago
      Geographic coordinates can change as well (earthquakes; office moves). But that's why the tzdb uses major/dominant city names.
      - samwillis 5y ago
        Issue with that is borders move, and with that time zones. So you may be in the “Europe/London” time zone now. But the north of England could annex itself and create a new time zone “Europe/Manchester”…
        ycombobreaker 5y ago
        In the great schism of England, the meeting organizer fled to central Europe, and one of the primary stakeholders died. The meeting had to be rescheduled.
        masklinn 5y ago
        A more realistic issue is that Scotland currently doesn’t have an entry in the tzdb.
    - layer8 5y ago
      Depends on what you mean by time zone info. A time zone identifier like "Europe/Paris" will usually be sufficient.
      - kalleboo 5y ago
        Time zone identifiers are created once an area stops matching the previously best-matched identifier. Like right now maybe all of France uses Europe/Paris, but if southern France decides to secede and change their timezone by an hour, they would all have to re-configure from Europe/Paris to Europe/Toulouse. The existence of a bunch of currently seemingly-redundant time zone identifiers are a testament to the history of this. So the only 100% future-proof solution is geographical coordinates that can be looked up in a continually-updated database.
  - wongarsu 5y ago
    > There’s really no such thing as exact time. The closest is UT1, and it’s not really practical for normal applications. I would have defined TAI (International Atomic Time) as "exact time". Of course with it being an average of clocks it only really exists in hindsight and isn't perfect either, but if you only need accuracy on the order of hundreds of nanoseconds and are on earth's surface it's very practical and easy to get anywhere with a GPS receiver. And UTC is only slightly more messy, with its offset from TAI to keep it in sync with unpredictable UT1.
    - tialaramex 5y ago
      Right. It's crazy that we welded civil time to UT1 rather than TAI. TAI is ours, we made it, here on this rock we're all stuck on. Because time is necessarily relative to your position that's the best we could hope for. The astronomers are welcome to UT1, it's useful for them and I have no problem with that, but it's crazy to keep tweaking UTC to match UT1 rather than just letting it plod along mechanically forever with TAI. Abolish leap seconds.
      - layer8 5y ago
        This will create problems in the long term, because the delta-T due to the deceleration of earth’s rotation due to tidal friction increases quadratically [1]. Not saying it wouldn’t be practical, but that’s the debate, along with astronomer’s needs, satellite navigation etc. You can’t just ignore the relationship with earth’s rotation. [1] e.g. https://www.researchgate.net/figure/Observations-and-parabolic-t-of-T-versus-time-since-1620-after-Stephenson-and-Morrison_fig1_231080674 https://www.researchgate.net/figure/Observations-and-parabol...
        tialaramex 5y ago
        > You can’t just ignore the relationship with earth’s rotation. Doing so is, in fact, the point of abolishing leap seconds. Imagine that we have leap seconds, and the very worst case conceivable happens for an entire century. 100 summer leap seconds and 100 winter leap seconds, that's a total of just over three minutes by 2122. Can we tolerate such a disruption to the lives of ordinary people? Try asking anybody who lives in a place with Daylight Saving or "Summer" Time. They have twenty times more disruption and that happens twice every single year.
- thrdbndndn 5y ago
  I probably should read into details, but I don't get it from the summary at least. How would future time zone change or DST change affect it at all? Is it about your pre-defined time would no longer be aligned with a nice "local time" like 0:00? Because things would still happen at the exactly same "moment" (absolute time or time delta from now) in UTC regardless any TZ change isn't it?
  - pretendgeneer 5y ago
    The difference is something like storing your comment was made at 2022-03-13+10:40. Which the time zone has been set and will always be the same at as it's in the past vs "set an alarm for 2023-03-13+6:00" I want the to happen at 6:00am whether or not my country continues to observe day light saving time.
  - 0x0 5y ago
    Doesn't help you much if you stored an UTC for a future meeting, then the TZ rules change, and then you show up an hour late.
    - tshaddox 5y ago
      True, although if your meeting will be attended by people in multiple time zones, you need to declare the time in some absolute sense anyway.
  - liversage 5y ago
    You have a timestamp in the future for example the time when a session at a conference starts. If to want to store this as UTC you will have to convert it from the local timezone of the conference location. You can always go back from the UTC timestamp to the local timestamp by performing the reverse conversion EXCEPT if the conversion involves daylight savings and the rule changes between now and the event in the future. While rare, daylight savings rules sometimes do change. I guess even local timezone offsets might change once in a while. When this happens the conversion back from UTC to the local time will be wrong unless you keep track of more than the UTC timestamp.
- brewmarche 5y ago
  I’ve used wall clock time for the latter. Because what matters is the time in the wall clock for future events/meetings. I don’t know where exactly I picked up the word but I’ve used it here https://news.ycombinator.com/item?id=22968405 https://news.ycombinator.com/item?id=22968405
- Joeri 5y ago
  There are basically three types of time: coordinated time, based on atomic clocks, zoned time, derived from coordinated time by applying a time zone rule to calculate an offset, and wall clock time. The latter two are often lumped together as local time but are not in practice the same thing. TZ rules are one way, you can derive zoned time reliably from utc, but not the other way around unless you already know the offset. That’s why seemingly storing utc is best, because you can apply whatever tz rule you want. But, because tz rules are decided by politicians (dst, ramadan, date line shifts, it gets pretty weird, …) and often with little advance notice, tz rule databases are often wrong in practice, which means storing only utc and getting zoned time back out of it reliably is hard. That’s why storing iso timestamps with offsets is best. It preserves both utc and intended local (zoned) time at the time of storing, even if the tz database then changes. But this is not the author’s problem. They’re dealing with wall clock time. They want to store 9 am and have it always be 9 am, no matter what happens with time zones. That is easy: store the timestamp without tz info. But, the hard part is knowing the instant in time (in coordinated time). Wall clock times may occur twice, so there is no single reliable way to get utc out of it, and even when calculating utc the result can be wrong or become wrong due to the political insanity that is zoned time so it is dangerous to store it and rely on it. You see this problem with anything that schedules people or resources. Something scheduled at 9 am is always 9 am, but if the system needs to send a reminder at 9 am, when do you schedule it? There are no solutions I’m aware of without edge cases. I solved this for a reservation system by storing wall clock time without offsets and the time zone (id) of the resource separately, and then calculating zoned time for that zone and comparing it with the stored wall clock time every time the instant in time mattered. It worked but was tricky to code.
  - ivan_gammel 5y ago
    > Wall clock times may occur twice Rule number one and the only rule about wall clock times is that they are display style only and must never be stored. Local times must use 24 hour format.
    - brewmarche 5y ago
      I think they’re talking about times occurring twice due to DST.
- moltke 5y ago
  Yeah, also you can't compare UTC times without an almanac generated by a random natural process which means even without the cultural/political problem UTC is still by definition uncertain for future dates. AST is arguably better for this but doesn't get around the political problem.
- samwillis 5y ago
  I wander if we need a new version of ISO8601 which includes the TZ location rather than just an offset. Something like: 2022-03-13T07:21:39@Europe/London I suppose that’s also problematic if the location changes to a different time zone due to boundary changes. Maybe we need a coordinate (and planetary body) system: 2022-03-13T07:21:39@Earth/51.5055853,-0.1014699 We need that full frame of reference to be sure we will be correct in future.
  - dzikimarian 5y ago
    Well, there's also continental drift :-) honestly everything we do in IT is approximation - some of them are worth accounting for, many are not - at least for most of us.
  - mqus 5y ago
    I think tzdata timezone locations (like Europe/London) are fully sufficient, since they are tracking the clock everyone uses in that location. If the boundaries change, then so will the clock for everyone in that boundary, so the time probably won't be right regardless. I think coordinates are not completely right, since the time context is mostly political/cultural, not geographic. I don't have examples but I can easily imagine a border region specifying some event in a time that is referencing the region across the border.
    - samwillis 5y ago
      > I think coordinates are not completely right, since the time context is mostly political/cultural, not geographic. My point was that geopolitical boundaries change and so what the “major” city near by for reference is now, may be wrong in the future. Coordinates ensure it’s completely future proof from boundary changes and correct to an exact location.
      - nly 5y ago
        There will always be outliers. If you're scheduling a meeting in a small town on the border of 2 countries that honor different time-zones, then you're probably just going to have to pick one to be the source of truth, and hope it's the other side causing the disruption (changing their clocks) and they'll pay for the administrative overhead.
- wpietri 5y ago
  Yeah, human time is much more complicated than the strict linear clock. For certain past periods the former can generally be destructively mapped onto the latter. But as with a lot of things, programmers tend to confuse human intent with the post-intent "objective" record of the thing. Which I get, as the latter is much easier to deal with.
- alecbz 5y ago
  Yeah, this is exactly the difference between what I’d call “timestamps” (something that represents a particular instant in time), and what I’d call “datetimes”, things like “midnight on Jan 1, 2019”. I worked at an insurance startup that stored coverage period start/ends as timestamps, which ended up being semantically wrong. The coverage ends at midnight of the next year, wherever _you_ (technically the provider, I think) are. So there is no one single instant in time when coverage ends; a timestamp is the wrong data type for representing this.
  - lamontcg 5y ago
    If you've worked on a distributed team that had members in the UK/EU and the USA then you've run into this before when scheduling meetings. A 10am PDT America/Los_Angeles meeting is different from a 6pm BST Europe/London meeting which is different from a 5pm GMT meeting and those will jump around by an hour depending on time changes. If the bulk of your team is in Seattle then you probably want to pick the US timezone for the meeting and not GMT or London. If you have a manager in London, though, who sets up the meeting time in their local timezone then Seattle employees will notice tomorrow that the meeting is a hour later in Seattle than it normally is. If everyone shitcanned clocks jumping around twice a year this craziness would end.
    - throwaway894345 5y ago
      I think this is the inverse problem. In this case, timestamp is the appropriate data structure for the meeting and each person's client needs to convert it back to local time, accounting for things like daylight savings time and so on.
      - marcan_42 5y ago
        Recurring meetings cannot be expressed as timestamps (unless you want all the humans to hate you). The recurrence has to be expressed in terms of local time (and date/day of week) in some (ideally per-event) timezone.
        5y ago
- karmakaze 5y ago
  Sometimes local date-time (with optionally a location) is better. If I tag a photo as Christmas morning and upload it to a global photosharing site, I'd expect something that says when I took it in my local time and optionally location. I don't care about UTC or the photo's timestamp in the viewers timezone. If you want to make it complicated store UTC + offset (not timezone).
- 0x20cowboy 5y ago
  Just store an offset amount from the start point, create a tangent line against the curve of time, and then as the limit approaches zero… :)
- techdragon 5y ago
  I called this out in the “how all this relates to programming” part of a presentation I did a couple of years ago on the history of timekeeping.
svilen_dobrev 5y ago
Which is, store the original intent, and re-intrepret the cached-unified-value any time there is knowledge-context change. Might become bitemporal.
- adrian_b 5y ago
  The problem here is a confusion between 2 different data types and attempting to use one type instead of the other correct type, which is a trivial programming mistake. A future time moment may be a true time, known exactly, which must be stored as an UTC time. Otherwise, it may be an event defined in the official time of some place. This must be a distinct data type, because as mentioned in the article, the real time that corresponds to it cannot be known in advance, because the legislation defining the official time may change before the event takes place. Obviously this second data type must store the complete information that determines the event time, i.e. both the official local time and the place, so that, a short time before the event, the correct conversion to real time, i.e. UTC, can be done, and then the different local times for each participant may be computed, to be displayed in their schedules. Of course, for displaying, an estimated UTC time for the event can be computed since the beginning, but it should be updated when the event approaches. The only problem here is when you fail to recognize that the "time" for a future event that is scheduled in local time is not a time, and you attempt to store it using the wrong data type. Also one must keep in mind that for this second data type, for future events scheduled in local time, no arithmetic operations can be defined, like the difference between two times. The only operation that can be defined for this data type is conversion to and from UTC.
- stingraycharles 5y ago
  I don’t think this is bitemporal, though: they’re two different “views” of the same time.
hughrr 5y ago
I am a proponent of just standardising on UTC everywhere and making the humans deal with it at this point. No DST, no time zones. If you're in the east coast US then you have breakfast at 11:00 UTC. It's just a number! This realisation came from trying to work out how the hell to write an international scheduling system where the humans couldn't even work out how to schedule a meeting across three time zones.
- jrimbault 5y ago
  https://qntm.org/abolish https://qntm.org/abolish
  - spacemanmatt 5y ago
    I came here to make sure this article got mentioned. Not disappointed.
    - jrimbault 5y ago
      It's a great article for "youngsters" with abolitionist ambitions relative to timezones (like I am/was)
  - junon 5y ago
    My main gripe with this article that I've thought about for years now after initially reading it is that the example ignores reality. In the current system, how do I know what time it is in Australia? Two ways: I either look it up, or I know how many hours it is relative to me. Living in Germany now, I know my parents are -8 and some friends are -9. I still always have to do the math. This is still apriori knowledge necessary that the author just skims over Compare this to if there were no time zones. I would still need to know when their solar noon was. The amount of information I would need to retain is the exact same. Looking it up would also be just the same, except the lookup would tell us the sun position instead of the local time. Don't get me wrong, I mostly agree with the article's point. The example just never felt adequate.
    - 5y ago
anticristi 5y ago
Such a cool article! Reminds me of a tip I got from a friend: "Never ask the product owner what to do in an edge case." "So what should our product do if the definition of the official timezone in Amsterdam changes relative to UTC?"
- spacemanmatt 5y ago
  I think the point here is, timezones change regularly and it's not an edge case.
  - anticristi 5y ago
    I agree that it's not an edge case for devs, but I do think it's an edge case for POs in the EU. My friend was even struggling with conversations like "so what if the user hits save exactly when data coverage drops". Not an edge case for techies, but likely an edge case for POs.
    - jonathanlydall 5y ago
      > My friend was even struggling with conversations like "so what if the user hits save exactly when data coverage drops". My favourite PO response to a question like that is "Oh, I suppose that could happen, but it would be very rare so don't worry about that", at which point you need to re-iterate that even if rare, we still have to do decide what should happen, even if it's just showing an error to try again.
- rendall 5y ago
  > "Never ask the product owner what to do in an edge case." Could you expand on that? I've found it best to keep PMs informed even about edge cases, but I'm curious to know what your friend meant? I usually don't ask, but say something like "We store time as UTC for future events, but apparently this is not the best practice because when timezone rules change, the local time is not updated. Setting up that refactor will be medium complexity. I recommend we take the time to fix it now, so that we will not have awkward moments for event organizers as people show up an hour early."
dkbrk 5y ago
Or... we could store Terrestrial Time and treat leap seconds as part of the timezone data. This isn't a complete panacea, there would still be clock drift and updates could still cause time to be non-monotonic. But a timestamp would have a consistent meaning, and the TT->UTC conversion could be taken care of as just another step in localization. Why on Earth was the decision taken in the first place to have NTP operate off UTC?
- rzzzt 5y ago
  Swatch came up with the "at-time" but it didn't take off for some reason: https://en.wikipedia.org/wiki/Swatch_Internet_Time https://en.wikipedia.org/wiki/Swatch_Internet_Time
isoprophlex 5y ago
... for the purpose of this blog post I’ll assume that each member state has to decide whether they will “spring forward” one last time on March 28th 2021, then staying in permanent “summer time”, or “fall back” one last time on October 31st 2021, then staying in permanent “winter time”. Too bad this didn't come true ...
rswail 5y ago
I've said for ages that programmers only need two subjects to learn. Date and time programming and debugging. The first one leads naturally to the second one. The rules I follow: 1. If you're recording an event in the past, store it in UTC. 2. If you need to record the local time for something either in the past or the future, store it as the local time as it was (for the past), or the local time as it will be (for the future), plus a timezone (not an offset). 3. If you're displaying a timestamp, a) if you can, display UTC, b) if needed, display in local time using current conversion rules 4. If you are getting user input, a) if you can, get it in UTC, b) if not, get it using a timezone (not an offset) 5. If you are storing a date, then that is not a timestamp. A date is a period, so either just store the date without a timezone, or, if it's a date period in a local area, store the start and end timestamps with their timezones.
- knorker 5y ago
  > 1. If you're recording an event in the past, store it in UTC. Why not epoch? An integer always means epoch. A human readable timestamp, unless it's exactly ISO8601, is pretty much always ambiguous. > 2. [...] plus a timezone (not an offset). Why not offset? ISO8601 is a good standard. And it takes a very special use case to care about leap seconds. For logging "leap smear" is clearly a better way to solve it, and for time measurements you should always use a monotonic clock, not wall clock. Oh, and with timezone I assume you mean something like "Europe/New_York", not ambiguous like "EST". > A date is a period, so either just store the date without a timezone Of course some dates don't exist in some timezones (and I mean more modern times than gregorian/julian). So you can't count days between two dates without knowing the timezone. In my opinion there are only two ways to store timestamps that are actually correct: epoch, or iso8601. Everything else will eventually lead to data corruption. (and even with these two you have to be careful)
  - samwillis 5y ago
    > Why not offset? An offset would have exactly the same issues as UTC. You need the time zone location to correct for future changes. Knowing it’s UTC+2 doesn’t help with that. Ultimately it comes down to intent, either relative to space time, or the socially excepted time in a particular location in the future. A time zone represented as a location/city name is the best we currently have, but even can can be wrong if the time zone boundary’s change.
stephen_g 5y ago
So many issues you can run into with time! One that really annoys me is the Health app on iPhone, where it records step count and other data, and does it well if you stay in the same timezone. But when you change timezone, the data is shown in the zone you’re in now (I assume it’s stored in UTC and then converted to the current timezone). Which is technically correct, but then looking back, it looks like I was doing a whole lot of walking from 9pm to 11am for a few weeks while I was on a trip (it was actually just a regular daily schedule), which then messes up the daily average step count, because the day boundaries aren’t what they actually were there. I’d really rather have an option to show it in the timezone that I was in at the time, and then on the daily resolution graphs mark where the time zone changed (with the hours in the difference either skipped or repeated depending on which way).
deleted 5y ago
[deleted]
pinetlk 5y ago
The tricky part is the conversion process from the server to the client. As JavaScript doesn't have any way to make dates with specific timezones other than the local timezone, you have to do some things to make up for it. In my last project, I convert the time using the timezone information I have stored by doing t at time zone x in postgres, so that it shows the time at that location, but then when it gets to the client, the actual Date object underneath is in the local timezone, even though it is displaying the time in a different timezone. It doesn't really matter because it is just for display purposes essentially. When I send the time back to the server, I strip the timezone information from the date object and add in my own timezone information to a newly created string in a postgres-specific format and send it back.
VMtest 5y ago
Not very often that system timestamp and UTC related posts sometimes appear on HN front page I think 3 days ago I was checking on it again, because I was wondering how websites put timestamp in their published RSS/atom feed https://hackerdaily.io https://hackerdaily.io RSS feed made me confused, to select timezone for the RSS feed as they want to think "Yesterday" means different thing for different timezone, not sure why, but I don't want to confused myself thinking how timezone and "Yesterday" work together HN is using UTC, I realized that by hovering the mouse pointer on the "2 hours ago" Again, if people don't want to use UTC for future events it's their choice. Personally I would advocate to use UTC and manually calculate the local datetime for future event, even if local timezone changes You know, I wonder how watches work in this case, how do you alter the time on your watches according to the DST changes? The mechanical engineer of the watch takes this bug into account?
- RubenvanE 5y ago
  The creator of https://hackerdaily.io https://hackerdaily.io here! The reason for a timezone specific RSS feed is because, like the site itself, the RSS feed only updates once a day at midnight. And _when_ the day changes depends on the timezone. Therefore we created different RSS feeds, one for each time zone.
- netsharc 5y ago
  Interestingly, technically the time of the event changed with the hypothetical TZ law changes. The event was going to be n hours from now, but with the TZ law change, it got moved to be n+1 hours away. If the convention hall could be booked hourly and had different events every hour, suddenly a slot would've opened up, at the 2 AM on the DST-change Sunday, the hour which would've not existed if the daylight savings law stayed the same...
  - VMtest 5y ago
    > If the convention hall could be booked hourly and had different events every hour, suddenly a slot would've opened up, at the 2 AM on the DST-change Sunday, the hour which would've not existed if the daylight savings law stayed the same... I did not think of this before...interesting....leave these problems to politicians, software engineers and end users to accommodate, I just want to be a naive programmer
XorNot 5y ago
Honestly at this point we as programmers shouldn't be storing "local time" or timezones, we should be storing latitude and longitude coordinates. Everyone has a GPS in their pockets, and timezones and their changes are determined by location anyway.
- VMtest 5y ago
  I assume you mean UTC plus the latitude and longitude isn't UTC alone is enough?
  - XorNot 5y ago
    If you have a time for an event, then there's an encoded assumption about what you're trying to determine. Very few - I would say zero - events we want to know when they happened but don't care about where in anyway. For example when planning meeting times, the reason local time comes up is ultimately some determinant about when it's going to be day or night - that's the actual metric that meeting planning generally tries to take into account. Strictly speaking I would say this also applies for storing data about machine to machine events which might be normally considered to be purely sequence based. If we're storing times to determine processing order for example, then there'd be value in storing the lat/lon of the machines generating them, because it's an extra datum which can resolve expected ordering (i.e. based back-calculating latency windows).
    - rswail 5y ago
      So if I (in Australia) have a meeting with people in Thailand and Slovakia over Google meet, which is absolutely an "event we want to know when they happened but don't care about where in anyway", the local time(s) are all important when scheduling it for the future, but as a record of the event, it happened in all 3 locations simultaneously.
      - XorNot 5y ago
        Sure, but if you're trying to derive any useful information from that record as opposed to some CYA audit then you really want to have some local situational context for it. Personally when I'm arranging a meeting I want to know the local cultural context, and I want to know the relative times I'm asking for things there. Getting stuff done 2 hours later may or may not be possible based on the local time of day, calendar day, and time of year.
math-dev 5y ago
Lisp has it done right (recommended reading for all): http://naggum.no/lugm-time.html http://naggum.no/lugm-time.html
deleted 5y ago
[deleted]
pritambarhate 5y ago
Here is how I deal with the Time in the systems I design: 1. Always store time in UTC 2. If the time is tied to a fixed place, like start of a Football match, store the timezone of the place in another column, for example, Asia/Kolkata, America/New_York, etc 3. Always store user's timezone as part of their preferences. Helps in use cases like send the reminder to the user at 8:00AM in their timezone. 4. All APIs always return time in UTC and user's or the places timezone in the output. 5. It's the job of the frontend to convert the UTC time to proper timezone and display it to the user. Never had a use case I couldn't solve with these rules.
- progval 5y ago
  > the timezone of the place It's not always unique. For example, Xinjiang uses both UTC+8 (Beijing time) and UTC+6 (Xinjiang Time), depending on context. https://en.wikipedia.org/wiki/Xinjiang_Time https://en.wikipedia.org/wiki/Xinjiang_Time Even when unique, it can be hard to automatically find it. The US and Canada typically have timezones that don't exactly follow state/province borders (or even county borders). https://en.wikipedia.org/wiki/Time_in_the_United_States#Boundaries_between_the_zones https://en.wikipedia.org/wiki/Time_in_the_United_States#Boun...
  - marcosdumay 5y ago
    Well, the rule to follow when you can't discover something by yourself is: ask the user. And for most things, even if you can discover it, let the user override your guess.
  - hackernewds 5y ago
    That just means your data should have more context than Xinjiang. This is like saying the continent of Europe doesnt use the same tz, since the geo data logged is only in "continent" units
- marcosdumay 5y ago
  Do you store scheduled times, when the UTC representation of the local time can change between the times the user entering it and the event happening?
WinterMount223 5y ago
Semi-serious question: At what speed does time propagate away from UTC? Do systems take into account relativistic effects when synchronizing across the earth, given that “Now” does not propagate instantaneously?
uptime 5y ago
good article! I have been using their Option 3: Store what the clock should say on the wall at either the physical location or the organizers desk if it is a call. Then store the timezone string for that location and calc the UTC from that to give local start times for other users. Some apps store that UTC in the row and get re-calc’d as needed. With other apps we create views or materialized views and update rows for UTC to give a single source for sorting, etc. Storing the rules version is an improvement I will look into!
janto 5y ago
For past events, I always beg people to somehow explicitly include the timezone in an encoding or storage, even if it's just UTC. i.e. never be tz naive! A tz naive PG column is a mess waiting to happen.
mabbo 5y ago
TL;dr: an instant in time and a future time based on a wall clock in a location aren't the same thing. But in my view, it's okay to pretend they are the same thing and model them all as instants. Even in the current climate of potentially large changes coming to the TZ database, I doubt there are many developers who will come out ahead trying to model this difference correctly vs manually handling any edge case misses. More likely, you'll have bugs in your implementation because time logic is awful.
bob1029 5y ago
No, its not a silver bullet but it almost is. For a vast majority of use cases, UTC storage is sufficient. For everything else, you need both the UTC timestamp and some other piece of knowledge depending on the scenario at hand. In many cases, its as simple as a User.UtcOffsetMinutes fact. In other more paranoid scenarios, you may be inclined to store the original timezone/offset in which the UTC fact was originally valid. Regardless of the scenario, there is no situation where I would want to store a non-UTC timestamp. Everything is UTC with some optional extras.
nurettin 5y ago
The answer is simple, store the time July 10th 2022 7am utc on march 27th. If you are using a countdown timer, reload the zoneinfo database everytime it changes, so that the countdown clock recalculates remaining time correctly. This article is about the difficulty of internationalizing countdowns, not the problem of storing times as UTC.
plank 5y ago
The situation the author is considering, is a future event in some geographical location in which the rules for the timezone change. In my honest opinion this is a non-solvable problem. So yes, storing in UTC or storing in local time is not a silver bullet. Because, depending on the event, there is a different wherewolve. If the event is e.g. the opening hours of a store, chances are that local time works. Indeed: if e.g. the Netherlands change the time zone rules, the local shop may continue to use the 09:00 hour local time as the time to opening the shops. But if it is e.g. the time that the last ‘window’ of delivering a package to the delivery company such that it will be on time for delivery to neighbouring country (say) Germany, then this will depend on the (changes to the rules of the) time zone in that country. Which may be different then the (changes to the rules of the) local time zone in which the event occurs. So, when planning ahead, one takes into account the knowledge of that time to make a assumption of when the event will take place. If the timezone rules change, one should reexamine which assumption works best. If the event is e.g. the time the moon will be eclipsed, UTC works best (without fails). If it is some local event not depended on anything outside the area in which the time zone rules apply, local time probably works best. But really, really knowing for certain? I would rather store (future) time in UTC and then consider whether changing time zone rules should or should not apply, as opposed to designing a system that could do this correctly in any situation…
lmilcin 5y ago
Storing UTC is not only not a silver bullet, in many cases it is just wrong. Timezone is an important context of the event. You can't tell if something happened on Wednesday if you do not know the timezone.
jimmytucson 5y ago
You don’t need to store timestamps “in UTC”. You just need to store the time zone offset. That extra few bytes of precision representing where the timestamp sender is makes the sun location absolute. Some databases let you input values like “2022-03-13T08:33:26-06:00” and then display them in UTC or your local time zone or whatever time zone you configured. BugQuery at one time was toying with the idea of taking a few additional bytes of information to represent the preferred display timezone of the timestamp, but I have no idea if that ever made it to beta.
- HideousKojima 5y ago
  That won't work for everything. For example: 1) User schedules a meeting 6 months out. Their time zone is -5 from UTC 2) 3 months later, their local politicians change their timezone to be -4.5 from UTC 3) The database has the wrong time since it has -5 stored and people miss the meeting by 30 minutes
p2p_astroturf 5y ago
[dead]
mro_name 5y ago
Why throw away the timezone ...+02:00? That's information loss.
TedShiller 5y ago
A good way to think about it is: dentist appointment time vs rocket launch time.
exabrial 5y ago
Ok here's a little bit of a guilty confession.... we _love_ MySQL's `timestamp null` type. MySQL certainly has some areas that aren't pretty, but we use the hell of out this. The `timestamp null` time automatically adjusts for the connecting user's timezone. Internally, IIRC it stores the value as UTC, but then converts it on the fly for wherever you're sitting. Why is this awesome? Well, because not everyone is a programmer or expert in time zone, daylight savings, understands UTC, etc. When our non-tech CEO wants to look at a report, he expects it to be relative to the time shown on his watch, which is exactly what this type does. Is it perfect? No. But does it solve a simple problem eloquently and prevent non-technical users from making bone-headed mistakes? yep.
- avalys 5y ago
  That sounds horrible, unless the CEO is writing and executing SQL statements on his own, directly against the database. If I'm sitting in Seattle VPN'd into a network that geolocates to South Carolina, and I send an HTTP request that terminates on a front-end box in Texas and sends a SQL query to a database in Ireland, what timezone will be used in the result? And why is this better than just making the same calculation in the UI layer?
  - exabrial 5y ago
    In that scenario, your browser communicates the timezone to the box in Texas, which sets the DB connection timezone to Seattle and the Irish database does the conversion for you. The VPN doesn't really factor in. Example: An order comes in 22:00 UTC, he's sitting in Seattle talking to a parts supplier. Said supplier want to know when the last order came in, so CEO pulls a sales report. It appears on the report as 3pm. This is intuitive and works every single time. Ironically, that scenario you describe would be quite complex to implement with a ui tweak. What if your data entry is a webapp, the CEO is using Metabase, and your data analyst is using Tableau? You know have to implement timezones 'correctly' in three different pieces of software, sometimes with software you didn't write.
mcronce 5y ago
A few companies ago, we were (essentially) a TSDB marketed toward network performance in particular. We had some large telecom companies as customers. We stored all the time-series in UTC, but the analytics data shifted by an hour twice a year. This caused a whole lot of false alarms for about a week following both time changes. This was a solid ten years ago. I don't think the problem was ever solved in the general case. We wrote them a script that support would manually run and shift all the analytics forward or back by an hour.
throwaway0x7E6 5y ago
is this really from 2019? I could swear I saw this years ago

243 points by nickkell 5y ago

This reminds me that Atlassian still doesn't support per-user date time formatting for some of their products (I use iso8601 where possible to avoid confusion in multinational settings). ಠ︵ಠ

samwillis 5y ago

I think summary here is that if you are storing an exact moment in time then UTC does the job. If however you are storing a future date and time in a certain location, due to the uncertainty about future daylight saving time changes you have to save it with time zone information in order to adjust for future changes to that system. I suppose it’s the difference between exact time and social/political time (there is probably a better term for that).

em3rgent0rdr 5y ago
> "social/political time (there is probably a better term for that)." The better term is "civil time": https://en.wikipedia.org/wiki/Civil_time https://en.wikipedia.org/wiki/Civil_time
masklinn 5y ago
> due to the uncertainty about future daylight saving time changes It’s not just DST, it’s any TZ-related changes: locations can change TZ for other reasons than dst. > I suppose it’s the difference between exact time and social/political time. There’s really no such thing as exact time. The closest is UT1, and it’s not really practical for normal applications.
- samwillis 5y ago
  > It’s not just DST, it’s any TZ-related changes: locations can change TZ fir other reasons than dst. Exactly, and for that time zone info will not be enough, you would need geographic coordinates.
  - deleted 5y ago
    [deleted]
  - spacemanmatt 5y ago
    To be more accurate you should also store relative velocity so the Lorentz transformation can be applied at display time
  - scatters 5y ago
    Geographic coordinates can change as well (earthquakes; office moves). But that's why the tzdb uses major/dominant city names.
    - samwillis 5y ago
      Issue with that is borders move, and with that time zones. So you may be in the “Europe/London” time zone now. But the north of England could annex itself and create a new time zone “Europe/Manchester”…
      - ycombobreaker 5y ago
        In the great schism of England, the meeting organizer fled to central Europe, and one of the primary stakeholders died. The meeting had to be rescheduled.
      - masklinn 5y ago
        A more realistic issue is that Scotland currently doesn’t have an entry in the tzdb.
  - layer8 5y ago
    Depends on what you mean by time zone info. A time zone identifier like "Europe/Paris" will usually be sufficient.
    - kalleboo 5y ago
      Time zone identifiers are created once an area stops matching the previously best-matched identifier. Like right now maybe all of France uses Europe/Paris, but if southern France decides to secede and change their timezone by an hour, they would all have to re-configure from Europe/Paris to Europe/Toulouse. The existence of a bunch of currently seemingly-redundant time zone identifiers are a testament to the history of this. So the only 100% future-proof solution is geographical coordinates that can be looked up in a continually-updated database.
- wongarsu 5y ago
  > There’s really no such thing as exact time. The closest is UT1, and it’s not really practical for normal applications. I would have defined TAI (International Atomic Time) as "exact time". Of course with it being an average of clocks it only really exists in hindsight and isn't perfect either, but if you only need accuracy on the order of hundreds of nanoseconds and are on earth's surface it's very practical and easy to get anywhere with a GPS receiver. And UTC is only slightly more messy, with its offset from TAI to keep it in sync with unpredictable UT1.
  - tialaramex 5y ago
    Right. It's crazy that we welded civil time to UT1 rather than TAI. TAI is ours, we made it, here on this rock we're all stuck on. Because time is necessarily relative to your position that's the best we could hope for. The astronomers are welcome to UT1, it's useful for them and I have no problem with that, but it's crazy to keep tweaking UTC to match UT1 rather than just letting it plod along mechanically forever with TAI. Abolish leap seconds.
    - layer8 5y ago
      This will create problems in the long term, because the delta-T due to the deceleration of earth’s rotation due to tidal friction increases quadratically [1]. Not saying it wouldn’t be practical, but that’s the debate, along with astronomer’s needs, satellite navigation etc. You can’t just ignore the relationship with earth’s rotation. [1] e.g. https://www.researchgate.net/figure/Observations-and-parabolic-t-of-T-versus-time-since-1620-after-Stephenson-and-Morrison_fig1_231080674 https://www.researchgate.net/figure/Observations-and-parabol...
      - tialaramex 5y ago
        > You can’t just ignore the relationship with earth’s rotation. Doing so is, in fact, the point of abolishing leap seconds. Imagine that we have leap seconds, and the very worst case conceivable happens for an entire century. 100 summer leap seconds and 100 winter leap seconds, that's a total of just over three minutes by 2122. Can we tolerate such a disruption to the lives of ordinary people? Try asking anybody who lives in a place with Daylight Saving or "Summer" Time. They have twenty times more disruption and that happens twice every single year.
thrdbndndn 5y ago
I probably should read into details, but I don't get it from the summary at least. How would future time zone change or DST change affect it at all? Is it about your pre-defined time would no longer be aligned with a nice "local time" like 0:00? Because things would still happen at the exactly same "moment" (absolute time or time delta from now) in UTC regardless any TZ change isn't it?
- pretendgeneer 5y ago
  The difference is something like storing your comment was made at 2022-03-13+10:40. Which the time zone has been set and will always be the same at as it's in the past vs "set an alarm for 2023-03-13+6:00" I want the to happen at 6:00am whether or not my country continues to observe day light saving time.
- 0x0 5y ago
  Doesn't help you much if you stored an UTC for a future meeting, then the TZ rules change, and then you show up an hour late.
  - tshaddox 5y ago
    True, although if your meeting will be attended by people in multiple time zones, you need to declare the time in some absolute sense anyway.
- liversage 5y ago
  You have a timestamp in the future for example the time when a session at a conference starts. If to want to store this as UTC you will have to convert it from the local timezone of the conference location. You can always go back from the UTC timestamp to the local timestamp by performing the reverse conversion EXCEPT if the conversion involves daylight savings and the rule changes between now and the event in the future. While rare, daylight savings rules sometimes do change. I guess even local timezone offsets might change once in a while. When this happens the conversion back from UTC to the local time will be wrong unless you keep track of more than the UTC timestamp.
brewmarche 5y ago
I’ve used wall clock time for the latter. Because what matters is the time in the wall clock for future events/meetings. I don’t know where exactly I picked up the word but I’ve used it here https://news.ycombinator.com/item?id=22968405 https://news.ycombinator.com/item?id=22968405
Joeri 5y ago
There are basically three types of time: coordinated time, based on atomic clocks, zoned time, derived from coordinated time by applying a time zone rule to calculate an offset, and wall clock time. The latter two are often lumped together as local time but are not in practice the same thing. TZ rules are one way, you can derive zoned time reliably from utc, but not the other way around unless you already know the offset. That’s why seemingly storing utc is best, because you can apply whatever tz rule you want. But, because tz rules are decided by politicians (dst, ramadan, date line shifts, it gets pretty weird, …) and often with little advance notice, tz rule databases are often wrong in practice, which means storing only utc and getting zoned time back out of it reliably is hard. That’s why storing iso timestamps with offsets is best. It preserves both utc and intended local (zoned) time at the time of storing, even if the tz database then changes. But this is not the author’s problem. They’re dealing with wall clock time. They want to store 9 am and have it always be 9 am, no matter what happens with time zones. That is easy: store the timestamp without tz info. But, the hard part is knowing the instant in time (in coordinated time). Wall clock times may occur twice, so there is no single reliable way to get utc out of it, and even when calculating utc the result can be wrong or become wrong due to the political insanity that is zoned time so it is dangerous to store it and rely on it. You see this problem with anything that schedules people or resources. Something scheduled at 9 am is always 9 am, but if the system needs to send a reminder at 9 am, when do you schedule it? There are no solutions I’m aware of without edge cases. I solved this for a reservation system by storing wall clock time without offsets and the time zone (id) of the resource separately, and then calculating zoned time for that zone and comparing it with the stored wall clock time every time the instant in time mattered. It worked but was tricky to code.
- ivan_gammel 5y ago
  > Wall clock times may occur twice Rule number one and the only rule about wall clock times is that they are display style only and must never be stored. Local times must use 24 hour format.
  - brewmarche 5y ago
    I think they’re talking about times occurring twice due to DST.
moltke 5y ago
Yeah, also you can't compare UTC times without an almanac generated by a random natural process which means even without the cultural/political problem UTC is still by definition uncertain for future dates. AST is arguably better for this but doesn't get around the political problem.
samwillis 5y ago
I wander if we need a new version of ISO8601 which includes the TZ location rather than just an offset. Something like: 2022-03-13T07:21:39@Europe/London I suppose that’s also problematic if the location changes to a different time zone due to boundary changes. Maybe we need a coordinate (and planetary body) system: 2022-03-13T07:21:39@Earth/51.5055853,-0.1014699 We need that full frame of reference to be sure we will be correct in future.
- dzikimarian 5y ago
  Well, there's also continental drift :-) honestly everything we do in IT is approximation - some of them are worth accounting for, many are not - at least for most of us.
- mqus 5y ago
  I think tzdata timezone locations (like Europe/London) are fully sufficient, since they are tracking the clock everyone uses in that location. If the boundaries change, then so will the clock for everyone in that boundary, so the time probably won't be right regardless. I think coordinates are not completely right, since the time context is mostly political/cultural, not geographic. I don't have examples but I can easily imagine a border region specifying some event in a time that is referencing the region across the border.
  - samwillis 5y ago
    > I think coordinates are not completely right, since the time context is mostly political/cultural, not geographic. My point was that geopolitical boundaries change and so what the “major” city near by for reference is now, may be wrong in the future. Coordinates ensure it’s completely future proof from boundary changes and correct to an exact location.
    - nly 5y ago
      There will always be outliers. If you're scheduling a meeting in a small town on the border of 2 countries that honor different time-zones, then you're probably just going to have to pick one to be the source of truth, and hope it's the other side causing the disruption (changing their clocks) and they'll pay for the administrative overhead.
wpietri 5y ago
Yeah, human time is much more complicated than the strict linear clock. For certain past periods the former can generally be destructively mapped onto the latter. But as with a lot of things, programmers tend to confuse human intent with the post-intent "objective" record of the thing. Which I get, as the latter is much easier to deal with.
alecbz 5y ago
Yeah, this is exactly the difference between what I’d call “timestamps” (something that represents a particular instant in time), and what I’d call “datetimes”, things like “midnight on Jan 1, 2019”. I worked at an insurance startup that stored coverage period start/ends as timestamps, which ended up being semantically wrong. The coverage ends at midnight of the next year, wherever _you_ (technically the provider, I think) are. So there is no one single instant in time when coverage ends; a timestamp is the wrong data type for representing this.
- lamontcg 5y ago
  If you've worked on a distributed team that had members in the UK/EU and the USA then you've run into this before when scheduling meetings. A 10am PDT America/Los_Angeles meeting is different from a 6pm BST Europe/London meeting which is different from a 5pm GMT meeting and those will jump around by an hour depending on time changes. If the bulk of your team is in Seattle then you probably want to pick the US timezone for the meeting and not GMT or London. If you have a manager in London, though, who sets up the meeting time in their local timezone then Seattle employees will notice tomorrow that the meeting is a hour later in Seattle than it normally is. If everyone shitcanned clocks jumping around twice a year this craziness would end.
  - throwaway894345 5y ago
    I think this is the inverse problem. In this case, timestamp is the appropriate data structure for the meeting and each person's client needs to convert it back to local time, accounting for things like daylight savings time and so on.
    - marcan_42 5y ago
      Recurring meetings cannot be expressed as timestamps (unless you want all the humans to hate you). The recurrence has to be expressed in terms of local time (and date/day of week) in some (ideally per-event) timezone.
      - 5y ago
karmakaze 5y ago
Sometimes local date-time (with optionally a location) is better. If I tag a photo as Christmas morning and upload it to a global photosharing site, I'd expect something that says when I took it in my local time and optionally location. I don't care about UTC or the photo's timestamp in the viewers timezone. If you want to make it complicated store UTC + offset (not timezone).
0x20cowboy 5y ago
Just store an offset amount from the start point, create a tangent line against the curve of time, and then as the limit approaches zero… :)
techdragon 5y ago
I called this out in the “how all this relates to programming” part of a presentation I did a couple of years ago on the history of timekeeping.

svilen_dobrev 5y ago

Which is, store the original intent, and re-intrepret the cached-unified-value any time there is knowledge-context change. Might become bitemporal.

adrian_b 5y ago
The problem here is a confusion between 2 different data types and attempting to use one type instead of the other correct type, which is a trivial programming mistake. A future time moment may be a true time, known exactly, which must be stored as an UTC time. Otherwise, it may be an event defined in the official time of some place. This must be a distinct data type, because as mentioned in the article, the real time that corresponds to it cannot be known in advance, because the legislation defining the official time may change before the event takes place. Obviously this second data type must store the complete information that determines the event time, i.e. both the official local time and the place, so that, a short time before the event, the correct conversion to real time, i.e. UTC, can be done, and then the different local times for each participant may be computed, to be displayed in their schedules. Of course, for displaying, an estimated UTC time for the event can be computed since the beginning, but it should be updated when the event approaches. The only problem here is when you fail to recognize that the "time" for a future event that is scheduled in local time is not a time, and you attempt to store it using the wrong data type. Also one must keep in mind that for this second data type, for future events scheduled in local time, no arithmetic operations can be defined, like the difference between two times. The only operation that can be defined for this data type is conversion to and from UTC.
stingraycharles 5y ago
I don’t think this is bitemporal, though: they’re two different “views” of the same time.

hughrr 5y ago

I am a proponent of just standardising on UTC everywhere and making the humans deal with it at this point. No DST, no time zones. If you're in the east coast US then you have breakfast at 11:00 UTC. It's just a number! This realisation came from trying to work out how the hell to write an international scheduling system where the humans couldn't even work out how to schedule a meeting across three time zones.

jrimbault 5y ago
https://qntm.org/abolish https://qntm.org/abolish
- spacemanmatt 5y ago
  I came here to make sure this article got mentioned. Not disappointed.
  - jrimbault 5y ago
    It's a great article for "youngsters" with abolitionist ambitions relative to timezones (like I am/was)
- junon 5y ago
  My main gripe with this article that I've thought about for years now after initially reading it is that the example ignores reality. In the current system, how do I know what time it is in Australia? Two ways: I either look it up, or I know how many hours it is relative to me. Living in Germany now, I know my parents are -8 and some friends are -9. I still always have to do the math. This is still apriori knowledge necessary that the author just skims over Compare this to if there were no time zones. I would still need to know when their solar noon was. The amount of information I would need to retain is the exact same. Looking it up would also be just the same, except the lookup would tell us the sun position instead of the local time. Don't get me wrong, I mostly agree with the article's point. The example just never felt adequate.
  - 5y ago

anticristi 5y ago

Such a cool article! Reminds me of a tip I got from a friend: "Never ask the product owner what to do in an edge case." "So what should our product do if the definition of the official timezone in Amsterdam changes relative to UTC?"

spacemanmatt 5y ago
I think the point here is, timezones change regularly and it's not an edge case.
- anticristi 5y ago
  I agree that it's not an edge case for devs, but I do think it's an edge case for POs in the EU. My friend was even struggling with conversations like "so what if the user hits save exactly when data coverage drops". Not an edge case for techies, but likely an edge case for POs.
  - jonathanlydall 5y ago
    > My friend was even struggling with conversations like "so what if the user hits save exactly when data coverage drops". My favourite PO response to a question like that is "Oh, I suppose that could happen, but it would be very rare so don't worry about that", at which point you need to re-iterate that even if rare, we still have to do decide what should happen, even if it's just showing an error to try again.
rendall 5y ago
> "Never ask the product owner what to do in an edge case." Could you expand on that? I've found it best to keep PMs informed even about edge cases, but I'm curious to know what your friend meant? I usually don't ask, but say something like "We store time as UTC for future events, but apparently this is not the best practice because when timezone rules change, the local time is not updated. Setting up that refactor will be medium complexity. I recommend we take the time to fix it now, so that we will not have awkward moments for event organizers as people show up an hour early."

dkbrk 5y ago

Or... we could store Terrestrial Time and treat leap seconds as part of the timezone data. This isn't a complete panacea, there would still be clock drift and updates could still cause time to be non-monotonic. But a timestamp would have a consistent meaning, and the TT->UTC conversion could be taken care of as just another step in localization. Why on Earth was the decision taken in the first place to have NTP operate off UTC?

rzzzt 5y ago
Swatch came up with the "at-time" but it didn't take off for some reason: https://en.wikipedia.org/wiki/Swatch_Internet_Time https://en.wikipedia.org/wiki/Swatch_Internet_Time

isoprophlex 5y ago

... for the purpose of this blog post I’ll assume that each member state has to decide whether they will “spring forward” one last time on March 28th 2021, then staying in permanent “summer time”, or “fall back” one last time on October 31st 2021, then staying in permanent “winter time”. Too bad this didn't come true ...

rswail 5y ago

I've said for ages that programmers only need two subjects to learn. Date and time programming and debugging. The first one leads naturally to the second one. The rules I follow: 1. If you're recording an event in the past, store it in UTC. 2. If you need to record the local time for something either in the past or the future, store it as the local time as it was (for the past), or the local time as it will be (for the future), plus a timezone (not an offset). 3. If you're displaying a timestamp, a) if you can, display UTC, b) if needed, display in local time using current conversion rules 4. If you are getting user input, a) if you can, get it in UTC, b) if not, get it using a timezone (not an offset) 5. If you are storing a date, then that is not a timestamp. A date is a period, so either just store the date without a timezone, or, if it's a date period in a local area, store the start and end timestamps with their timezones.

knorker 5y ago
> 1. If you're recording an event in the past, store it in UTC. Why not epoch? An integer always means epoch. A human readable timestamp, unless it's exactly ISO8601, is pretty much always ambiguous. > 2. [...] plus a timezone (not an offset). Why not offset? ISO8601 is a good standard. And it takes a very special use case to care about leap seconds. For logging "leap smear" is clearly a better way to solve it, and for time measurements you should always use a monotonic clock, not wall clock. Oh, and with timezone I assume you mean something like "Europe/New_York", not ambiguous like "EST". > A date is a period, so either just store the date without a timezone Of course some dates don't exist in some timezones (and I mean more modern times than gregorian/julian). So you can't count days between two dates without knowing the timezone. In my opinion there are only two ways to store timestamps that are actually correct: epoch, or iso8601. Everything else will eventually lead to data corruption. (and even with these two you have to be careful)
- samwillis 5y ago
  > Why not offset? An offset would have exactly the same issues as UTC. You need the time zone location to correct for future changes. Knowing it’s UTC+2 doesn’t help with that. Ultimately it comes down to intent, either relative to space time, or the socially excepted time in a particular location in the future. A time zone represented as a location/city name is the best we currently have, but even can can be wrong if the time zone boundary’s change.

stephen_g 5y ago

So many issues you can run into with time! One that really annoys me is the Health app on iPhone, where it records step count and other data, and does it well if you stay in the same timezone. But when you change timezone, the data is shown in the zone you’re in now (I assume it’s stored in UTC and then converted to the current timezone). Which is technically correct, but then looking back, it looks like I was doing a whole lot of walking from 9pm to 11am for a few weeks while I was on a trip (it was actually just a regular daily schedule), which then messes up the daily average step count, because the day boundaries aren’t what they actually were there. I’d really rather have an option to show it in the timezone that I was in at the time, and then on the daily resolution graphs mark where the time zone changed (with the hours in the difference either skipped or repeated depending on which way).

deleted 5y ago

[deleted]

pinetlk 5y ago

The tricky part is the conversion process from the server to the client. As JavaScript doesn't have any way to make dates with specific timezones other than the local timezone, you have to do some things to make up for it. In my last project, I convert the time using the timezone information I have stored by doing t at time zone x in postgres, so that it shows the time at that location, but then when it gets to the client, the actual Date object underneath is in the local timezone, even though it is displaying the time in a different timezone. It doesn't really matter because it is just for display purposes essentially. When I send the time back to the server, I strip the timezone information from the date object and add in my own timezone information to a newly created string in a postgres-specific format and send it back.

VMtest 5y ago

Not very often that system timestamp and UTC related posts sometimes appear on HN front page I think 3 days ago I was checking on it again, because I was wondering how websites put timestamp in their published RSS/atom feed https://hackerdaily.io https://hackerdaily.io RSS feed made me confused, to select timezone for the RSS feed as they want to think "Yesterday" means different thing for different timezone, not sure why, but I don't want to confused myself thinking how timezone and "Yesterday" work together HN is using UTC, I realized that by hovering the mouse pointer on the "2 hours ago" Again, if people don't want to use UTC for future events it's their choice. Personally I would advocate to use UTC and manually calculate the local datetime for future event, even if local timezone changes You know, I wonder how watches work in this case, how do you alter the time on your watches according to the DST changes? The mechanical engineer of the watch takes this bug into account?

RubenvanE 5y ago
The creator of https://hackerdaily.io https://hackerdaily.io here! The reason for a timezone specific RSS feed is because, like the site itself, the RSS feed only updates once a day at midnight. And _when_ the day changes depends on the timezone. Therefore we created different RSS feeds, one for each time zone.
netsharc 5y ago
Interestingly, technically the time of the event changed with the hypothetical TZ law changes. The event was going to be n hours from now, but with the TZ law change, it got moved to be n+1 hours away. If the convention hall could be booked hourly and had different events every hour, suddenly a slot would've opened up, at the 2 AM on the DST-change Sunday, the hour which would've not existed if the daylight savings law stayed the same...
- VMtest 5y ago
  > If the convention hall could be booked hourly and had different events every hour, suddenly a slot would've opened up, at the 2 AM on the DST-change Sunday, the hour which would've not existed if the daylight savings law stayed the same... I did not think of this before...interesting....leave these problems to politicians, software engineers and end users to accommodate, I just want to be a naive programmer

XorNot 5y ago

Honestly at this point we as programmers shouldn't be storing "local time" or timezones, we should be storing latitude and longitude coordinates. Everyone has a GPS in their pockets, and timezones and their changes are determined by location anyway.

VMtest 5y ago
I assume you mean UTC plus the latitude and longitude isn't UTC alone is enough?
- XorNot 5y ago
  If you have a time for an event, then there's an encoded assumption about what you're trying to determine. Very few - I would say zero - events we want to know when they happened but don't care about where in anyway. For example when planning meeting times, the reason local time comes up is ultimately some determinant about when it's going to be day or night - that's the actual metric that meeting planning generally tries to take into account. Strictly speaking I would say this also applies for storing data about machine to machine events which might be normally considered to be purely sequence based. If we're storing times to determine processing order for example, then there'd be value in storing the lat/lon of the machines generating them, because it's an extra datum which can resolve expected ordering (i.e. based back-calculating latency windows).
  - rswail 5y ago
    So if I (in Australia) have a meeting with people in Thailand and Slovakia over Google meet, which is absolutely an "event we want to know when they happened but don't care about where in anyway", the local time(s) are all important when scheduling it for the future, but as a record of the event, it happened in all 3 locations simultaneously.
    - XorNot 5y ago
      Sure, but if you're trying to derive any useful information from that record as opposed to some CYA audit then you really want to have some local situational context for it. Personally when I'm arranging a meeting I want to know the local cultural context, and I want to know the relative times I'm asking for things there. Getting stuff done 2 hours later may or may not be possible based on the local time of day, calendar day, and time of year.

math-dev 5y ago

Lisp has it done right (recommended reading for all): http://naggum.no/lugm-time.html http://naggum.no/lugm-time.html

deleted 5y ago

[deleted]

pritambarhate 5y ago

Here is how I deal with the Time in the systems I design: 1. Always store time in UTC 2. If the time is tied to a fixed place, like start of a Football match, store the timezone of the place in another column, for example, Asia/Kolkata, America/New_York, etc 3. Always store user's timezone as part of their preferences. Helps in use cases like send the reminder to the user at 8:00AM in their timezone. 4. All APIs always return time in UTC and user's or the places timezone in the output. 5. It's the job of the frontend to convert the UTC time to proper timezone and display it to the user. Never had a use case I couldn't solve with these rules.

progval 5y ago
> the timezone of the place It's not always unique. For example, Xinjiang uses both UTC+8 (Beijing time) and UTC+6 (Xinjiang Time), depending on context. https://en.wikipedia.org/wiki/Xinjiang_Time https://en.wikipedia.org/wiki/Xinjiang_Time Even when unique, it can be hard to automatically find it. The US and Canada typically have timezones that don't exactly follow state/province borders (or even county borders). https://en.wikipedia.org/wiki/Time_in_the_United_States#Boundaries_between_the_zones https://en.wikipedia.org/wiki/Time_in_the_United_States#Boun...
- marcosdumay 5y ago
  Well, the rule to follow when you can't discover something by yourself is: ask the user. And for most things, even if you can discover it, let the user override your guess.
- hackernewds 5y ago
  That just means your data should have more context than Xinjiang. This is like saying the continent of Europe doesnt use the same tz, since the geo data logged is only in "continent" units
marcosdumay 5y ago
Do you store scheduled times, when the UTC representation of the local time can change between the times the user entering it and the event happening?

WinterMount223 5y ago

Semi-serious question: At what speed does time propagate away from UTC? Do systems take into account relativistic effects when synchronizing across the earth, given that “Now” does not propagate instantaneously?

uptime 5y ago

good article! I have been using their Option 3: Store what the clock should say on the wall at either the physical location or the organizers desk if it is a call. Then store the timezone string for that location and calc the UTC from that to give local start times for other users. Some apps store that UTC in the row and get re-calc’d as needed. With other apps we create views or materialized views and update rows for UTC to give a single source for sorting, etc. Storing the rules version is an improvement I will look into!

janto 5y ago

For past events, I always beg people to somehow explicitly include the timezone in an encoding or storage, even if it's just UTC. i.e. never be tz naive! A tz naive PG column is a mess waiting to happen.

mabbo 5y ago

TL;dr: an instant in time and a future time based on a wall clock in a location aren't the same thing. But in my view, it's okay to pretend they are the same thing and model them all as instants. Even in the current climate of potentially large changes coming to the TZ database, I doubt there are many developers who will come out ahead trying to model this difference correctly vs manually handling any edge case misses. More likely, you'll have bugs in your implementation because time logic is awful.

bob1029 5y ago

No, its not a silver bullet but it almost is. For a vast majority of use cases, UTC storage is sufficient. For everything else, you need both the UTC timestamp and some other piece of knowledge depending on the scenario at hand. In many cases, its as simple as a User.UtcOffsetMinutes fact. In other more paranoid scenarios, you may be inclined to store the original timezone/offset in which the UTC fact was originally valid. Regardless of the scenario, there is no situation where I would want to store a non-UTC timestamp. Everything is UTC with some optional extras.

nurettin 5y ago

The answer is simple, store the time July 10th 2022 7am utc on march 27th. If you are using a countdown timer, reload the zoneinfo database everytime it changes, so that the countdown clock recalculates remaining time correctly. This article is about the difficulty of internationalizing countdowns, not the problem of storing times as UTC.

plank 5y ago

The situation the author is considering, is a future event in some geographical location in which the rules for the timezone change. In my honest opinion this is a non-solvable problem. So yes, storing in UTC or storing in local time is not a silver bullet. Because, depending on the event, there is a different wherewolve. If the event is e.g. the opening hours of a store, chances are that local time works. Indeed: if e.g. the Netherlands change the time zone rules, the local shop may continue to use the 09:00 hour local time as the time to opening the shops. But if it is e.g. the time that the last ‘window’ of delivering a package to the delivery company such that it will be on time for delivery to neighbouring country (say) Germany, then this will depend on the (changes to the rules of the) time zone in that country. Which may be different then the (changes to the rules of the) local time zone in which the event occurs. So, when planning ahead, one takes into account the knowledge of that time to make a assumption of when the event will take place. If the timezone rules change, one should reexamine which assumption works best. If the event is e.g. the time the moon will be eclipsed, UTC works best (without fails). If it is some local event not depended on anything outside the area in which the time zone rules apply, local time probably works best. But really, really knowing for certain? I would rather store (future) time in UTC and then consider whether changing time zone rules should or should not apply, as opposed to designing a system that could do this correctly in any situation…

lmilcin 5y ago

Storing UTC is not only not a silver bullet, in many cases it is just wrong. Timezone is an important context of the event. You can't tell if something happened on Wednesday if you do not know the timezone.

jimmytucson 5y ago

You don’t need to store timestamps “in UTC”. You just need to store the time zone offset. That extra few bytes of precision representing where the timestamp sender is makes the sun location absolute. Some databases let you input values like “2022-03-13T08:33:26-06:00” and then display them in UTC or your local time zone or whatever time zone you configured. BugQuery at one time was toying with the idea of taking a few additional bytes of information to represent the preferred display timezone of the timestamp, but I have no idea if that ever made it to beta.

HideousKojima 5y ago
That won't work for everything. For example: 1) User schedules a meeting 6 months out. Their time zone is -5 from UTC 2) 3 months later, their local politicians change their timezone to be -4.5 from UTC 3) The database has the wrong time since it has -5 stored and people miss the meeting by 30 minutes

p2p_astroturf 5y ago

[dead]

mro_name 5y ago

Why throw away the timezone ...+02:00? That's information loss.

TedShiller 5y ago

A good way to think about it is: dentist appointment time vs rocket launch time.

exabrial 5y ago

Ok here's a little bit of a guilty confession.... we _love_ MySQL's `timestamp null` type. MySQL certainly has some areas that aren't pretty, but we use the hell of out this. The `timestamp null` time automatically adjusts for the connecting user's timezone. Internally, IIRC it stores the value as UTC, but then converts it on the fly for wherever you're sitting. Why is this awesome? Well, because not everyone is a programmer or expert in time zone, daylight savings, understands UTC, etc. When our non-tech CEO wants to look at a report, he expects it to be relative to the time shown on his watch, which is exactly what this type does. Is it perfect? No. But does it solve a simple problem eloquently and prevent non-technical users from making bone-headed mistakes? yep.

avalys 5y ago
That sounds horrible, unless the CEO is writing and executing SQL statements on his own, directly against the database. If I'm sitting in Seattle VPN'd into a network that geolocates to South Carolina, and I send an HTTP request that terminates on a front-end box in Texas and sends a SQL query to a database in Ireland, what timezone will be used in the result? And why is this better than just making the same calculation in the UI layer?
- exabrial 5y ago
  In that scenario, your browser communicates the timezone to the box in Texas, which sets the DB connection timezone to Seattle and the Irish database does the conversion for you. The VPN doesn't really factor in. Example: An order comes in 22:00 UTC, he's sitting in Seattle talking to a parts supplier. Said supplier want to know when the last order came in, so CEO pulls a sales report. It appears on the report as 3pm. This is intuitive and works every single time. Ironically, that scenario you describe would be quite complex to implement with a ui tweak. What if your data entry is a webapp, the CEO is using Metabase, and your data analyst is using Tableau? You know have to implement timezones 'correctly' in three different pieces of software, sometimes with software you didn't write.

mcronce 5y ago

A few companies ago, we were (essentially) a TSDB marketed toward network performance in particular. We had some large telecom companies as customers. We stored all the time-series in UTC, but the analytics data shifted by an hour twice a year. This caused a whole lot of false alarms for about a week following both time changes. This was a solid ten years ago. I don't think the problem was ever solved in the general case. We wrote them a script that support would manually run and shift all the analytics forward or back by an hour.

throwaway0x7E6 5y ago

is this really from 2019? I could swear I saw this years ago