18 ms·

Storing UTC is not a silver bullet

304 points by AndrewDucker 8y ago

Ahmed90 8y ago
I did a full switch to Postgres like 2 years ago, and a decent chunk of the decision was `timestamp with timezone` saved me a huge amount of headaches working on multinational services Use timestamp with tz, drop Unix timestamps and use ISO8601 DateTime format across the application ezpz never look back
- benmmurphy 8y ago
  I think PG will exhibit the problems that skeet was referring to because PG stores the UTC time and not the local time. If someone makes a change to the timezone rules then the times your columns are referring to will stay the same in UTC time which might not be what you want. https://www.postgresql.org/docs/9.1/datatype-datetime.html https://www.postgresql.org/docs/9.1/datatype-datetime.html "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone." 'timestamp with timezone' is kind of a gotcha. i guess it combines two things into one type that are logically one type but it also might mislead someone into believing the type is something else.
- emilsedgh 8y ago
  Unfortunately `timestamp with time zone` is misleading and crippled. When you store something as timestamptz, postgres converts it to UTC and "loses" the timezone part. The only difference between timestamptz and timestamp is that when postgres is returning the timestamptz to you, it converts it back to your local time zone defined by SET TIME ZONE (and not the time zone it was originally stored in) So basically if you store a timestamptz, you cannot convert it back to the local time, as the local time is never even stored. I personally had to add a second `timezone` column to my table to store the timezone of the date.
  - colanderman 8y ago
    I wouldn't go so far as to say the timestamp types are broken, just confusingly named. `timestamp with time zone` signifies an absolute point in time; `timestamp without time zone` signifies a timestamp relative to some unspecified time zone (which you must store separately, as you point out). The builtin functions universally adhere to these semantics. E.g., when you do store the timezone separately, use `timestamp without time zone`: `time AT TIME ZONE timezone` [1] only does the right thing (i.e., interpreting `time` as relative to `timezone`, and producing an absolute time corresponding to it) if `time` is `without time zone`. (If `time` is `with time zone`, it produces the local time corresponding to the absolute time `time`, which is mathematically exactly the opposite of what you want.) [1] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT https://www.postgresql.org/docs/current/functions-datetime.h...
    - emilsedgh 8y ago
      I said they are crippled, not broken. Unfortunately the poor naming is a big faux pas. I really really expected timestamp with time zone to store the time zone.
- funcDropShadow 8y ago
  Additionally, you have to be careful with the Postgres JDBC driver. When you store or load to a timestamp column, it compares the JVM local time zone with Postgres' session time zone and translates appropriately. Even when you have a test that makes sure that you get back the same timestamp that you put into it, it might be the wrong timestamp if you use psql to look at it.
- ams6110 8y ago
  Unix timestamps are almost always the wrong choice to represent date/time, unless you're doing something directly involving a unix filesystem.
- snuxoll 8y ago
  timstamptz is fine for historical data and certain kinds of “this will (need to )happen at this time” situations, but as others have said it just converts to/from UTC based on your session time zone - this makes it inadequate for calendaring. For calendaring or other applications where time zone’s shifting offsets can cause issues you need to store the UTC time along with the timezone and then do the conversion on read.
kstenerud 8y ago
What about: * ID: auto-incremented integer * Name: string * StartTime: date/time in UTC * Address: string * TimeZoneId: Europe/Amsterdam * RuleEffectiveTime: date/time in UTC The rule effective time represents when the start time was last changed, converted to UTC. Next, create a time engine that can convert between events (time + location). The engine can convert events to different time zones in different epochs, to UTC, or the other way around, because it knows all the rules, and when the rules changed throughout history. A simple API will handle things: time time_engine.parse_time(rule_effective_time_event, string_representation) String time_engine.to_presentation_string(rule_effective_time_event, time_value_utc) Where time_event is a tuple consisting of the rule effective time, and the timezone whose rules to apply. When the venue changes, you update TimeZoneId. When the start time changes, you change StartTime and RuleEffectiveTime. The time engine would only be invoked to present the start time in current time + zone format for the user to modify in their own temporal space, then to parse the changed time back to UTC using the user location + new RuleEffectiveTime (which is now). Timezone rules are fixed as events in time and space, so every rule effective time + time zone + utc_time will uniquely point to a specific time zone rule, which means that the rule itself doesn't need to be stored in the database. Timestamps are also fixed, and should never change (unless the user changes the time for the meeting, of course). You could build the time engine to preserve absolute time (14:00 becomes 15:00 after the rule change), or to preserve local time (14:00 remains 14:00 after the rule change, even though it's technically a different time). You could even change how the engine behaves after data has been created, since it's only affecting how things are interpreted locally to the user. The data remains the same no matter what. The database's role is to store data, not to decide rules. Store the data, not the rules.
koboll 8y ago
This really buries the lede, which is 'Use timestamps'.
- protomyth 8y ago
  Well, yes, but you really need to add some information to it. The last system I worked on that really needed dates for transactions and reporting stored: local time, local time zone, local offset from UTC at time of event, and UTC time stamp. Its amazing how complicated your life can get when reporting across time zones and then mixing that with legal requirements for some reporting. Also, time zone id and local time is insufficient unless you keep reference tables that can calculate what offset was in effect at the time of the event. Save the trouble and just record it along with the time stamp.
- tirpen 8y ago
  Which would have the exact same problem as UTC. For most use cases when a users enter a datetime in the future, they do not care about how many seconds from 1970-01-01 that point is, they care that it occurs at exactly 12:30 on that particular day.
  - 0815test 8y ago
    If you really cared about how many seconds have passed since 1970-01-01, you'd need to use TAI not UTC.
- lelf 8y ago
  And get the same problems, plus the new one, leap seconds (timestamps ignore them).
- cryptonector 8y ago
  It's not. The lede is "store intended time w/ TZ, and also compute and recompute UTC timestamps as needed".
rlpb 8y ago
This is the same as the alarm clock problem. When a user creates an event, what does that user _mean_? In the alarm clock example, what should happen if I've set my alarm clock 7am and then travel to a different time zone? In the conference event example, what should happen if the local time zone rules change, or daylight savings time comes into force? Did the event organiser print marketing materials that said "starts at 1000 UTC" or "starts at 10am" with a "in the local time zone" implication? Events generally need to be locked to _something_ - whether that is UTC or a local time zone depends on the application. The iCalendar standard (https://tools.ietf.org/html/rfc5545 https://tools.ietf.org/html/rfc5545) generally gets this right.
- maxxxxx 8y ago
  That’s what annoys me about google calendar and most others. When I schedule an appointment most of the times it will be for the time zone i will be in at the time of the appointment. So when I travel and agree to meet people at 10 I want to enter it that way. But in most calendar apps everything gets shifted around as soon as I arrive at the new location. I would much prefer to have apps not shift stuff around unless I explicitly tell the app to do it.
  - nfriedly 8y ago
    Google Calendar does let you set the time zone in the 'More options' view, FWIW. I use it occasionally.
    - maxxxxx 8y ago
      I know. But I want no time zone. When I plan my Germany trip next week I want to see the 10 am meeting there at 10 regardless of where I am. It should operate like a good old paper calendar.
      - harpastum 8y ago
        Google Calendar is built around notifications and coordination, which both require an agreed upon "actual" time. The feature set you're looking for sounds more like an "agenda" to me.
HugoDaniel 8y ago
This is also a related article: https://zachholman.com/talk/utc-is-enough-for-everyone-right https://zachholman.com/talk/utc-is-enough-for-everyone-right It is more high level but a very good read on the subject.
- ReliantGuyZ 8y ago
  Autoplaying video right at the top. Never closed a tab so fast.
  - drekembemutombo 8y ago
    Funny you should say that, here's an excerpt from the article: > And then someone will calmly quote this passage in response, quietly pleased with themselves that the initial commenter was rude and certainly didn’t read the post at all. Then a third person will chime in on the thread saying the author was playing you all like a fiddle anyway, and the real problem is that the post was way too long to start with. It's funny that it predicts my comment as well, touché.
argd678 8y ago
Birthdays are a bit tricky too, say you want to show if it’s someone’s birthday today. In UTC you can be off by a day, so you just have to store the date without the TZ and treat it local in every TZ.
lmm 8y ago
Storing UTC (or rather TAI) is a silver bullet for "system" times - things that need to happen on a regular schedule, where we care how much time has passed between one instant and another, or things where we want to record at what instant something happened, and correlate system events with each other. Indeed it doesn't solve the problem of handling user-inputted "human" times correctly. But that is a much rarer case than needing to handle "system" times. Many - probably most - applications never need to handle a "human" time at all.
- seniorsassycat 8y ago
  I prefer TIA over UTC and wish it was widely used. Leap seconds mean the same UTC time can happen twice if we fall back a second, or a UTC time may never happen when we skip ahead a second. Leap seconds also mean that epoch milliseconds derived from UTC do not represent milliseconds between the timestamp and Jan 1 1970 unless the conversion is aware of all leap seconds. Leap seconds are prescribed every six months with six month notice. There are many time keeping devices that are not updated as often as UTC leap seconds are introduced. I'm pretty happy with ISO-8601, but dates after the year 10,000 will not sort lexicographical. A 64 bit signed int storing milliseconds since Jan 1 1970 covers 29225733 B.C to 29229672 A.D
  - Macross8299 8y ago
    >There are many time keeping devices that are not updated as often as UTC leap seconds are introduced. Isn't this an argument against using TAI in certain applications though? i.e. if you're designing a system which relies on non-leapsecond-updated devices to be in sync with devices that are capable of being updated, you should always design as such to accommodate the former rather than the latter?
    - lmm 8y ago
      No? It's an argument for using TAI,because devices that use TAI do not need leap second updates - a clock running TAI that was started in 1970 and never updated will show the same time as a clock running TAI that's started today (or one that was started in 1970 and updated continually), whereas a clock running UTC that wasn't updated will show a different time from one that was updated.
seniorsassycat 8y ago
> Time zone boundary changes and splits The united states will experience this soon. California and Washington are independently seeking to end daylight saving time. There is no America/Portland or America/Seattle so Washingtonians and Oregonians are using America/Los_Angeles. Unless Washington, California, and Oregon end Daylight savings on the same schedule we'll have to add new id's and users will have to change their configuration, and we'll still have issues described in the link.
deleted 8y ago
[deleted]
kuon 8y ago
I think it depends on what the time represents. When a user inputs a time, let's say for an alarm, this is always "wall clock", and this is what you store. The timezone can be stored too, but this is also dynamic. For example, a user put a conference in a calendar at 7 am, this is dependent of the user location the day of the conference. For app following the user, this is simple (like a mobile app), you just use the current timezone of the system the app is running on, but for a distributed system (client server), it might be complicated to pick a timezone and is dependent of the application. For computer times (like logging, cron...), just use UTC.
barrystaes 8y ago
Dont confuse UTC with GMT. Store absolutely everything in UTC, do all calculations on UTC, and show in whatever timeunit the user wants. The one edge-case where user inputs a local time thats is not unique (repeated hour due to daylight savings time) can easily be solved consistently, and often is not even a problem at all.
- em500 8y ago
  This is bad advice if you need to store future events/appointments. Because in the majority of cases the meaning of the future datetime is "the date/times on the majority of the calendars and clocks on the wall in the vicinity of the event". The implication is that if the local timezone rules change between now and the future event, the user wants the local datetime of the appointment to stay unchanged, which implies that the UTC/epoch version of the appointment has to be changed. Generating the correct datetimes from UTC/epoch+local timezone requires a lot more administration and logic than storing just the local datetime. (You need at least both the current TZ database and the version when the appointment was made/last changed to have a hope to do this correctly.)
- deleted 8y ago
  [deleted]
- nulbyte 8y ago
  That's not the only edge case, and the blog points that out.
- philwelch 8y ago
  I used to think that, when I was young and naive, which was maybe a month or so ago. Here's a use case: You and I are agreeing to meet at a given landmark. This could be any landmark in the United States, just to keep it simple. The user experience is: you propose that we meet at <landmark> at 4 PM tomorrow, and I agree. This happens on a website. As far as you, me, and most of the system is concerned, the only sensible way to store that time is as a timezone-agnostic, UTC-agnostic local time: "2019-03-28T16:00". That's because, for the system to store that time as UTC, we have to know the UTC offset of every landmark in the United States at any point in time (which isn't an easy problem!) and consistently, correctly convert between UTC and that local time every time it gets displayed anyway. If we screwed up the UTC offset when we stored it the first time, we have to change the UTC time. Now, if there's an additional constraint that you can only schedule meeting times that are actually in the future, you have to know what times constitute "the future" relative to the UTC offset of the landmark, so you don't get out of needing a comprehensive UTC offset map of the United States. But if you screw that up, you just end up putting an irrelevant or impossible meeting time in a dropdown and maybe dealing with some users attempting to time travel. Even that is a failure mode that users will somewhat understand, but most of the time it won't matter unless users are scheduling within the same time window as your UTC error. If you stored it in UTC, you'd be screwing it up all the damn time until you fixed your UTC offsets, at which point previously-working data would be incorrect because someone would schedule a meeting 8 days out in one of those geographic regions for 6 PM and suddenly wonder how it got shifted to 5 PM for no reason. Now, if we're scheduling a meeting online, and videoconferencing instead of traveling to the Navajo Reservation in Arizona--of course we store it in UTC. And if some of us are traveling to the Navajo Reservation[1] and some of us are dialing in from Newfoundland[2], we're all fucked anyway. [1] The Navajo Indian Reservation spans multiple states, including Arizona. Arizona does not observe DST. The Navajo Indian Reservation does. However, inside the Arizona portion of the Navajo reservation, there is a Hopi Indian Reservation, which does not observe DST. Inside of the Hopi reservation, there is another part of the Navajo reservation that does observe DST. [2] Newfoundland's UTC offset isn't an integer number of hours, there's an extra half hour in there. Enjoy!
barrystaes 8y ago
PSA: this article is confusing UTC with GMT.
- deleted 8y ago
  [deleted]
- seniorsassycat 8y ago
  The author did not confuse GMT for UTC. I live in Seattle, I set a reminder on my phone one minute before new years 2021. date --date "2021-01-01T07:59+00:00" Thu Dec 31 23:59:00 PST 2020 Currently Washington follows daylight saving time so we'll be using PST on new years eve. Washington lawmakers are considering a bill to end daylight saving time. If it passes then new years eve 2020 will be PDT, not PST 2021-01-01T07:59+00:00 is midnight PST but it is 1:00 AM PDT and my alarm will be off by an hour.
PaulHoule 8y ago
What is more obscure but equally obnoxious is that dates are also affected by time zones. For instance, Emperor Hirohito went to bed on December 8, 1942. A few hours later, bombers attacked Pearl Harbor at December 7 local time.
- testplzignore 8y ago
  Things get really fun when you are asked to be able to sort these ambiguous dates. Let's say Event A happens in New York (Eastern Time Zone) at 2018-12-08 02:00 UTC, and is recorded as occurring on 2018-12-07 (with no time) local time. Then let's say Event B happens in London (GMT) at 2018-12-08 01:00 UTC, and is recorded as occurring on 2018-12-08 (with no time) local time. Which event happened first? If we know the UTC times, a reasonable person would say Event B happened first. But if we just have the local dates with no times, we either say that Event A happened first, or we say we don't know. This is a real problem I have to deal with and it is a pain. One user says A happened first, another says B happened first, another says A and B happened at the same time, and I say "give me the UTC time or you don't get this feature" :)
  - jrochkind1 8y ago
    Then you can talk about Russia's Gregorian calendar switch. The day the Russian revolution began on 25 October 1917, it was 7 November 1917 in England.
    - oftenwrong 8y ago
      $ cal 9 1752 September 1752 Su Mo Tu We Th Fr Sa 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
      - jrochkind1 8y ago
        Not in Russia that wasn't the calendar in September of 1752! Apparently the `cal` command assumes certain geographic areas when it shows you calendars...
reaperducer 8y ago
Time is so messy, and that messiness becomes apparent when the yes/no, true/false world of computers tries to interpret things. The blog posts brings up a lot of good points about time zones changing, and the comments make even better points about people moving across time zones. Something that confounds me occasionally is the concept of "local human time" because there are all sorts of ways that humans tamper with time. Lawmakers, for example, that are required to pass certain laws by a particular date will sometimes unplug clocks in the legislative chambers to stop the legal passage of time while negotiations continue. You end up in a situation where, on paper the next day or week, 134 laws were negotiated, voted on, ratified, re-voted, passed, and signed all at exactly $date:23:59:59. A physical impossibility, but a legal fact. Or, as I explore the more interesting corners of America more, there are entire towns and regions that ignore their official time zone and every house, business, church, pharmacy, etc... is on a more convenient time for the locals. The only place you see the official time is at the post office. People are messy. Time is messy. People * time = a complete mess.
- sebazzz 8y ago
  > Lawmakers, for example, that are required to pass certain laws by a particular date will sometimes unplug clocks in the legislative chambers to stop the legal passage of time while negotiations continue. You end up in a situation where, on paper the next day or week, 134 laws were negotiated, voted on, ratified, re-voted, passed, and signed all at exactly $date:23:59:59. A physical impossibility, but a legal fact. Is this internationally accepted or a USA-only thing? It seems like fraud to me.
  - philwelch 8y ago
    The polite term is "legal fiction".
  - PeterisP 8y ago
    I don't know about that particular description, but it kind of makes sense - two bills passed on 27th March parliament session are passed on the same "business day" even if that day gets long and one of them was voted on five minutes before midnight and the other was voted on five minutes past midnight; and if there are some mandated legal delays (e.g. you have x days to before they'll be in force or something) then both bills will be treated as if they were passed on the same date. This also matches common usage - if I tell you what I did on a friday night, it'll include both things before and after midnight. There are also parallels in banking where business days don't align with calendar days, and you may have (depending on regulations and agreements) either a "long friday" or "long monday" where an action that's physically performed on a sunday will be treated with an effective date of friday or monday respectively; or possibly events after the closure of a business day may be legally treated as happened in the day after.
shikoba 8y ago
It's because a date and a time are not the same thing. Storing date as a UTC time is of course always wrong.
edoo 8y ago
If you have ever done anything seriously with time zones you store everything UTC and must use a library to translate. The problem is it is geopolitical, so the time zone conversion result can vary based on the date itself as well as the physical location. For example DST was changed in 2007 in the US, there is no option but to programmatically translate.
- jobigoud 8y ago
  Even if you store in UTC and use a library you have to know what the user mean when they create a recurring appointment. I currently have several recurring alerts in my calendar app. One of them is an alarm everyday at 10 p.m. This must be in local time everywhere I go. Another is a conference call at 10H30 a.m on Wednesdays, Pacific Time (not my timezone). If you treat them the same way one of them will be wrong when I travel.
sudhirj 8y ago
There was another discussion here 10 months ago after Zach Holman's post: https://news.ycombinator.com/item?id=17181046 https://news.ycombinator.com/item?id=17181046 Sort of a summarization of most of the ideas here is: * for clear instants like a system log UTC makes sense * for times in the past (far enough in the past that you know your current TZ rules are correct) UTC makes sense. * for the immediate past (near enough where your rules might not be correct) you might want to store zone as well. For all cases of times in the past, remember that you're trying to record an instant. An exact unambiguous point in time that is the same irrespective of who is looking at it or where they are. For this UTC makes sense - people who see that instant converted to their local timezone are noting what their clocks would have shown at that instant, but everyone who will ever see it is always referencing the same instant. FUTURE times are very different. There are times at which you want to reference a future instant, at which point you can just use UTC, but most often what you really have is "this is what I expect to see on my wall clock when this thing happens". That is a very different beast, because nobody knows what the timezone rules or geopolitical situation will be when that thing does happen. You need to recognize the use cases where what you mean is " I expect to see XX:YY:ZZ on my wall clock when this happens" and just record that information as is, including as much information as you can about that wall clock - possibly which timezone it (currently) uses, where it is (in case the timezone itself changes) and then convert to an instant at the last possible moment, or keep updating your instant based on changing rules.
- IgorPartola 8y ago
  There is one thing that I ran into with past times that I think falls outside of this. When you record things like “first day of employment”, you really do mean the date and not the datetime, and the date should be interpreted in whatever the local timezone is. Theoretically your first datetime if employment is what, midnight of the day you show up? Or is it 8am or whatever when you actually first step through the door? Each situation will be interpreted differently by the parties involved so best you can do is to store the date and the location/timezone of where they work, even though it’s in the past.
  - sudhirj 8y ago
kazinator 8y ago
I don't see how the countdown timer can fail to reach zero when the conference starts, if we have calculated the correct number of seconds between now and the conference, and do not change the conference time based on whether or not daylight savings time is canceled, to preserve it apparent local time. If we decide that the conference's start may be a moving target based on changing DST rules, then our coutdown timer cannot smoothly count down. At every tick, it has to evaluate the amount of time left to the conference start, and show that. The conference start's local time is always converted to UTC based on the current rules; when the rules change, it jumps by one hour and so the countdown timer adjusts itself at the next tick to show an extra hour. You can't smoothly count-down toward something that starts at X or at X + 1h, and you don't know which until later.
- fendy3002 8y ago
  Which is why storing the time data in UTC isn't a correct approach, because it just become countdown timer. A user may need to be reminded at specific time in a future, on a specific time zone. And it can't be reached by merely put a countdown timer.
  - kazinator 8y ago
    That doesn't follow. If the DST rules change, then, effectively, any event whose local time stays the same has actually moved. The sun will be in a different spot of the sky when that meeting is held than it would have been. All the dates stored in UTC which move due to a DST rule change have to be traversed and updated, as do any dependent countdown timers. That doesn't mean it wasn't "correct" to store them that way. "Correct" means that we handle all the cases in our specifications and design, and they are translated to code without mistakes. People like countdown timers. If there is going to be a countdown timer to an event whose time is uncertain, that countdown timer has to update itself when the exact time is pinned down. Until then, it should probably use the shorter countdown so that it errs on the side of too early. It has to be updated whether the time is stored as local time or as UTC. The behavior of the countdown timer cannot depend on how the time is stored in some database.
ble 8y ago
The distinction between different kinds of time is really pretty key to having a clear understanding. Civil time is different from atomic time and different from sidereal time or solar time. Civil time is how people, businesses, and governments organize and plan their activities. Atomic time is monotonic, physical, and "more objective" / less dependent on legislatures :) Sidereal time is about the orientation of Earth relative to stars. It's really important if you're an astronomer. UTC is a civil time standard based closely on TAI, an atomic time standard, except it gets corrected to be within 1 second of UT1, a sidereal time standard. It is basically _the_ civil time standard and it is a little compromise to make a civil time that advances like atomic time but also does not desync from sidereal time. Different administrative regions use offsets from UTC to determine their local civil time. If you're writing an application where people plan future activities and you really want to "get things right / not surprise users", you probably need to store times in the appropriate civil time standard, make it apparent _which_ civil time standard is canonical per activity if people are being coordinated across administrative regions that do not synchronize their standards, and ensure that your application can find out about planned changes to civil time in any of the administrative regions of interest.
crooked-v 8y ago
The "time zone version" issue mentioned in the article isn't quite as hard as it looks, as time zone-based libraries will (if done right) include the whole history of time zone changes too. For example, java.time and the JS lib moment-timezone both use a full time zone history to convert to/from UTC, not just current offsets.
- jonskeet 8y ago
  I think you've misunderstood what's changing here. It's not a matter of time zones not knowing that rules vary across time. It's a matter of knowing that "the rules for 2022" vary over time as well - in that the rules I might predict for 2022 may well not be the case now. "Time zone history" (in every platform I've seen) does not mean "knowing what the rules for 2022 were in 2019". They mean "getting things right according to all the currently-known rules, across all of history". That's very different.
amenod 8y ago
Of course. The real advice should be: - store past dates in UTC - store future dates in the format that makes sense, based on what the truth is. Scheduling event at 9AM local time? Use local time. Determining when the spacecrafts meet? Use UTC (or Federation Standard Time if you prefer ;) ).
jrochkind1 8y ago
I believe the problems described here are with future dates, and future timezone definition changes you don't know about 'right now' when you converting a local time to UTC for storage. The nature of the problem is that when you convert from a local timestamp to UTC, you might have been "wrong" in the conversion. Or, more specifically, it may have been as correct today, but it may become wrong some time in between now and when the timestamp occurs, because of future time zone definition changes. A future timezone definition change may mean you should have converted to UTC differently, which you couldn't have known because the timezone definition change happened after you converted. (Usually statutorily; sometimes maybe because your timezone data was wrong and was later corrected?) I don't believe these problems apply to _past_ dates or "right now" timestamps. Unless your timezone data is wrong I guess. Does this seem right?
- NateEag 8y ago
  I think it's close. As the article explains, though, present and past timestamps can be impacted too - your timezone library may be out of date at the moment you save the timestamp, or in some cases the legal body defining the timezone may reach into the past with a timezone change.
  - jrochkind1 8y ago
    How can a legal body possibly change the timezone retroactively? All those people on Monday that _thought_ it was 1pm, it turns on Tuesday we decided it was actually 2pm when they thought it was 1pm. But nobody could have known that on Monday. So they've got to... go back and change any times they put on documents on Monday? How does that make any sense? But incorrect timezone data or implementation is too real, true.
    - NateEag 8y ago
      Governments can do what they like, by and large. Just because it's crazy doesn't mean they won't, and nothing about physical reality prevents them from doing this. I'm not aware of any specific examples, so perhaps I'm being too paranoid in my first post, but it would not surprise me in the least to hear of, for example, a bill to eliminate DST in a state that somehow manages not to pass until a day or three after the DST change takes effect. There have at least been some some bizarre calendar changes that many software systems fail to accommodate, which probably feeds into my above paranoia: https://en.m.wikipedia.org/wiki/Soviet_calendar https://en.m.wikipedia.org/wiki/Soviet_calendar https://mentalfloss.com/article/51370/why-our-calendars-skipped-11-days-1752 https://mentalfloss.com/article/51370/why-our-calendars-skip...
karmakaze 8y ago
An interesting case was time stamps for photos on a global photo sharing site. UTC and conversion to local for display was the normal pattern. We decided to change this to display the time of the photo local to where the photo was taken. This where-taken and when-taken were paired. It made new years' and other holiday or time of day shots make sense.
SonicSoul 8y ago
I think a more correct title would have been "date storage antipatterns" storing in UTC is fine as long as your clients are time zone aware and can convert to local and send timestamps with time zone so that the server can convert back to UTC. the "options" provided are just examples of how not to do it.
mixmastamyk 8y ago
I noticed another use case regarding photos on the filesystem. I like to see them in their local datetimes, rather than the absolute time in my current location, but don't believe linux filesystems supports that. For example if I travel and take photos I don't want their mod times to be of those back home, which can be in the middle of the night or wrong day. Have been moving them to my local timezone via a time shift, but not sure that is the right thing to do. Now they are in my timezone, but if I move one day, will I have to update all my photo times? Even with exiftool and DateTimeOriginal, that's more file updates than I'd like to do.
- nybble41 8y ago
  The filesystem is doing the right thing here by accurately storing the original point in time when the photo was taken (or at least it was before you shifted it). If two files were modified simultaneously then they should have the same mod times, even if the changes occurred on opposite sides of the planet. The problem is all in the presentation layer; you want your file explorer (or 'ls') to display the times in their original timezone, not the timezone you're currently in. There is an EXIF tag for the time zone offset but support for it appears to be limited. The original timezone could be inferred from the EXIF location data, however, if you have that enabled.
revskill 8y ago
Why not just store both UTC and timezone right into the database ?
- FearNotDaniel 8y ago
  Because for future events whose time is fixed in their local timezone, you can't be certain that the timezone's offset from UTC on that future date is going to be the same as what you currently think it will be.
z3t4 8y ago
I like unix time. But its not perfect either. Soon we will have a new y2k problem where unix time can no longer fit into an integer. Anyone remember the y2k problem ? People predicted major catastrophes as the year was encoded with two digits. But no one is talking about unix time.
- tombert 8y ago
  This will certainly be a problem for older apps, but anything I've done in the last 10 years I've been dev'ing, literally every library I've used for Unix time uses a 64 bit integer, which should last for a few million years.
  - z3t4 8y ago
    Some programming languages still don't have 64 bit integers!
- feedbeef 8y ago
  I wonder what kind of fallout will come from the year 2038 problem [1]. It appears the Deep Impact space probe was lost to a 32-bit clock rollover bug, which portends some level of disaster come 2038. 1. https://en.wikipedia.org/wiki/Year_2038_problem https://en.wikipedia.org/wiki/Year_2038_problem
dheera 8y ago
On a related note, I do all of my personal time in UTC. All of my phones, clocks, watches, computers, calendars are set to UTC. It just makes everything easier for my personal projects.
kpmah 8y ago
Am I right in thinking that if you always store in UTC, when the clocks go back you will get overlapping data? e.g. if you store your logs with a UTC date you'll see interleaving logs for the extra hour, or if you read sensor data or something like that you'll get double readings?
- simcop2387 8y ago
  Well kind of. At least with how it works in the US. The first 2am is PST, the second 2am is PDT. so while they look the same, they're actually different times still. That said, rendering this for a user is still a pain depending on the UI.
- Qwertystop 8y ago
  No? Or not necessarily, at least. For example, pytz (a Python timezone library) offers "is_dst" flags for exactly that purpose. >>> loc_dt = datetime(2002, 10, 27, 1, 30, 00) >>> est_dt = eastern.localize(loc_dt, is_dst=True) >>> edt_dt = eastern.localize(loc_dt, is_dst=False) >>> print(est_dt.strftime(fmt) + ' / ' + edt_dt.strftime(fmt)) 2002-10-27 01:30:00 EDT-0400 / 2002-10-27 01:30:00 EST-0500 https://pythonhosted.org/pytz/#problems-with-localtime https://pythonhosted.org/pytz/#problems-with-localtime
  - kpmah 8y ago
    Yeah, I think I'm confusing storing things as UTC with storing things as UTC+0 - I forgot it stores the offset.
foxhop 8y ago
Store timestamp and convert it to whatever the user wants.
delfinom 8y ago
Why is this even a problem? This is the entire job of tzinfo which any respectable language uses to resolve datetime conversion.
nurettin 8y ago
As in save geographically bound event calendars with local time. Problem solved.
Scarblac 8y ago
In our application we can compare charts of timeseries data that may originate in many different places in the world. If you want to compare e.g. two charts of temperature over a 24-hour period between two places, you want noon for both to be in the same place on the X-axis. So you want to use local time of both places. But if you only store UTC, you don't know local time. No need for tzinfo changes or travelling through time zones.
jchw 8y ago
Storing UTC is still usually right because in many cases rather than dealing with calendar times, developers are dealing with instants in time that have already passed/are passing (creation/update timestamps, logs, etc.) I think this basically is only important when you might store a date in the future. If you have timezone rules incorrect for present or near past dates, well you're going to have a hell of a time dealing with that no matter what you do, really. Handling calendar times and recurring times have their own complications.
EGreg 8y ago
We simply use the google timezone API to find the event’s local timezone, for physical events, then store UTC. For non-physical events, we store UTC based on the timezone of the user agent that made the request, taking into account daylight savings rules. On the extremely rare occasion that rules change between when the event was scheduled and when it takes place, we don’t have a system in place, so the time would have to be updated manually. NOW... it gets even more interesting for recurring events. Does 11AM on Tue mean 11AM on Tue after daylight savings? How about events like Shabbat candle lighting times that depend on when the sun sets? It took us a while to do all these. Does anyone know a good non-API solution to timezone calculations / shapefiles feed?
kazagistar 8y ago
The silver bullet isn't storing UTC, its getting rid of timezones. Why not just have a universal clock and call it good?
- zbentley 8y ago
  Because people need to tell each other about time-related things while being globally distant. "Call me at dinnertime, say 7." "My 7 or your 7?" Is a lot easier than "Call me at 0400" and having both parties do offset math in their heads.
  - chrisseaton 8y ago
    I don't understand. Your first example is the one where people need to do offset maths. The second example requires no offset maths as it's 0400 for both parties and they both already know what they're doing at 0400 if we didn't have timezones.
xellisx 8y ago
https://github.com/evansiroky/timezone-boundary-builder https://github.com/evansiroky/timezone-boundary-builder
clhodapp 8y ago
I don't understand the described result for Option 1 or how having stored in UTC creates a problem there. It would seem to me like when the government changes the rules for local time, they are effectively moving the (real, not clock) time for the conference. It would seem like there is enough information stored in the database that installing the new time zone rules should cause the timer to move to accommodate this, as long as addresses don't get redefined (a different issue with that storage model). I don't see why the timer would not instantaneously jump an hour to correct itself when it becomes aware of the new rules or why it wouldn't hit the zero at the right time.
chaz6 8y ago
I have always thought that time would be best stored as milliseconds from TAI. It is a measure that is universally constant and unambiguous.

304 points by AndrewDucker 8y ago

I did a full switch to Postgres like 2 years ago, and a decent chunk of the decision was `timestamp with timezone` saved me a huge amount of headaches working on multinational services Use timestamp with tz, drop Unix timestamps and use ISO8601 DateTime format across the application ezpz never look back

benmmurphy 8y ago
I think PG will exhibit the problems that skeet was referring to because PG stores the UTC time and not the local time. If someone makes a change to the timezone rules then the times your columns are referring to will stay the same in UTC time which might not be what you want. https://www.postgresql.org/docs/9.1/datatype-datetime.html https://www.postgresql.org/docs/9.1/datatype-datetime.html "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone." 'timestamp with timezone' is kind of a gotcha. i guess it combines two things into one type that are logically one type but it also might mislead someone into believing the type is something else.
emilsedgh 8y ago
Unfortunately `timestamp with time zone` is misleading and crippled. When you store something as timestamptz, postgres converts it to UTC and "loses" the timezone part. The only difference between timestamptz and timestamp is that when postgres is returning the timestamptz to you, it converts it back to your local time zone defined by SET TIME ZONE (and not the time zone it was originally stored in) So basically if you store a timestamptz, you cannot convert it back to the local time, as the local time is never even stored. I personally had to add a second `timezone` column to my table to store the timezone of the date.
- colanderman 8y ago
  I wouldn't go so far as to say the timestamp types are broken, just confusingly named. `timestamp with time zone` signifies an absolute point in time; `timestamp without time zone` signifies a timestamp relative to some unspecified time zone (which you must store separately, as you point out). The builtin functions universally adhere to these semantics. E.g., when you do store the timezone separately, use `timestamp without time zone`: `time AT TIME ZONE timezone` [1] only does the right thing (i.e., interpreting `time` as relative to `timezone`, and producing an absolute time corresponding to it) if `time` is `without time zone`. (If `time` is `with time zone`, it produces the local time corresponding to the absolute time `time`, which is mathematically exactly the opposite of what you want.) [1] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT https://www.postgresql.org/docs/current/functions-datetime.h...
  - emilsedgh 8y ago
    I said they are crippled, not broken. Unfortunately the poor naming is a big faux pas. I really really expected timestamp with time zone to store the time zone.
funcDropShadow 8y ago
Additionally, you have to be careful with the Postgres JDBC driver. When you store or load to a timestamp column, it compares the JVM local time zone with Postgres' session time zone and translates appropriately. Even when you have a test that makes sure that you get back the same timestamp that you put into it, it might be the wrong timestamp if you use psql to look at it.
ams6110 8y ago
Unix timestamps are almost always the wrong choice to represent date/time, unless you're doing something directly involving a unix filesystem.
snuxoll 8y ago
timstamptz is fine for historical data and certain kinds of “this will (need to )happen at this time” situations, but as others have said it just converts to/from UTC based on your session time zone - this makes it inadequate for calendaring. For calendaring or other applications where time zone’s shifting offsets can cause issues you need to store the UTC time along with the timezone and then do the conversion on read.

kstenerud 8y ago

What about: * ID: auto-incremented integer * Name: string * StartTime: date/time in UTC * Address: string * TimeZoneId: Europe/Amsterdam * RuleEffectiveTime: date/time in UTC The rule effective time represents when the start time was last changed, converted to UTC. Next, create a time engine that can convert between events (time + location). The engine can convert events to different time zones in different epochs, to UTC, or the other way around, because it knows all the rules, and when the rules changed throughout history. A simple API will handle things: time time_engine.parse_time(rule_effective_time_event, string_representation) String time_engine.to_presentation_string(rule_effective_time_event, time_value_utc) Where time_event is a tuple consisting of the rule effective time, and the timezone whose rules to apply. When the venue changes, you update TimeZoneId. When the start time changes, you change StartTime and RuleEffectiveTime. The time engine would only be invoked to present the start time in current time + zone format for the user to modify in their own temporal space, then to parse the changed time back to UTC using the user location + new RuleEffectiveTime (which is now). Timezone rules are fixed as events in time and space, so every rule effective time + time zone + utc_time will uniquely point to a specific time zone rule, which means that the rule itself doesn't need to be stored in the database. Timestamps are also fixed, and should never change (unless the user changes the time for the meeting, of course). You could build the time engine to preserve absolute time (14:00 becomes 15:00 after the rule change), or to preserve local time (14:00 remains 14:00 after the rule change, even though it's technically a different time). You could even change how the engine behaves after data has been created, since it's only affecting how things are interpreted locally to the user. The data remains the same no matter what. The database's role is to store data, not to decide rules. Store the data, not the rules.

koboll 8y ago

This really buries the lede, which is 'Use timestamps'.

protomyth 8y ago
Well, yes, but you really need to add some information to it. The last system I worked on that really needed dates for transactions and reporting stored: local time, local time zone, local offset from UTC at time of event, and UTC time stamp. Its amazing how complicated your life can get when reporting across time zones and then mixing that with legal requirements for some reporting. Also, time zone id and local time is insufficient unless you keep reference tables that can calculate what offset was in effect at the time of the event. Save the trouble and just record it along with the time stamp.
tirpen 8y ago
Which would have the exact same problem as UTC. For most use cases when a users enter a datetime in the future, they do not care about how many seconds from 1970-01-01 that point is, they care that it occurs at exactly 12:30 on that particular day.
- 0815test 8y ago
  If you really cared about how many seconds have passed since 1970-01-01, you'd need to use TAI not UTC.
lelf 8y ago
And get the same problems, plus the new one, leap seconds (timestamps ignore them).
cryptonector 8y ago
It's not. The lede is "store intended time w/ TZ, and also compute and recompute UTC timestamps as needed".

rlpb 8y ago

This is the same as the alarm clock problem. When a user creates an event, what does that user _mean_? In the alarm clock example, what should happen if I've set my alarm clock 7am and then travel to a different time zone? In the conference event example, what should happen if the local time zone rules change, or daylight savings time comes into force? Did the event organiser print marketing materials that said "starts at 1000 UTC" or "starts at 10am" with a "in the local time zone" implication? Events generally need to be locked to _something_ - whether that is UTC or a local time zone depends on the application. The iCalendar standard (https://tools.ietf.org/html/rfc5545 https://tools.ietf.org/html/rfc5545) generally gets this right.

maxxxxx 8y ago
That’s what annoys me about google calendar and most others. When I schedule an appointment most of the times it will be for the time zone i will be in at the time of the appointment. So when I travel and agree to meet people at 10 I want to enter it that way. But in most calendar apps everything gets shifted around as soon as I arrive at the new location. I would much prefer to have apps not shift stuff around unless I explicitly tell the app to do it.
- nfriedly 8y ago
  Google Calendar does let you set the time zone in the 'More options' view, FWIW. I use it occasionally.
  - maxxxxx 8y ago
    I know. But I want no time zone. When I plan my Germany trip next week I want to see the 10 am meeting there at 10 regardless of where I am. It should operate like a good old paper calendar.
    - harpastum 8y ago
      Google Calendar is built around notifications and coordination, which both require an agreed upon "actual" time. The feature set you're looking for sounds more like an "agenda" to me.

HugoDaniel 8y ago

This is also a related article: https://zachholman.com/talk/utc-is-enough-for-everyone-right https://zachholman.com/talk/utc-is-enough-for-everyone-right It is more high level but a very good read on the subject.

ReliantGuyZ 8y ago
Autoplaying video right at the top. Never closed a tab so fast.
- drekembemutombo 8y ago
  Funny you should say that, here's an excerpt from the article: > And then someone will calmly quote this passage in response, quietly pleased with themselves that the initial commenter was rude and certainly didn’t read the post at all. Then a third person will chime in on the thread saying the author was playing you all like a fiddle anyway, and the real problem is that the post was way too long to start with. It's funny that it predicts my comment as well, touché.

argd678 8y ago

Birthdays are a bit tricky too, say you want to show if it’s someone’s birthday today. In UTC you can be off by a day, so you just have to store the date without the TZ and treat it local in every TZ.

lmm 8y ago

Storing UTC (or rather TAI) is a silver bullet for "system" times - things that need to happen on a regular schedule, where we care how much time has passed between one instant and another, or things where we want to record at what instant something happened, and correlate system events with each other. Indeed it doesn't solve the problem of handling user-inputted "human" times correctly. But that is a much rarer case than needing to handle "system" times. Many - probably most - applications never need to handle a "human" time at all.

seniorsassycat 8y ago
I prefer TIA over UTC and wish it was widely used. Leap seconds mean the same UTC time can happen twice if we fall back a second, or a UTC time may never happen when we skip ahead a second. Leap seconds also mean that epoch milliseconds derived from UTC do not represent milliseconds between the timestamp and Jan 1 1970 unless the conversion is aware of all leap seconds. Leap seconds are prescribed every six months with six month notice. There are many time keeping devices that are not updated as often as UTC leap seconds are introduced. I'm pretty happy with ISO-8601, but dates after the year 10,000 will not sort lexicographical. A 64 bit signed int storing milliseconds since Jan 1 1970 covers 29225733 B.C to 29229672 A.D
- Macross8299 8y ago
  >There are many time keeping devices that are not updated as often as UTC leap seconds are introduced. Isn't this an argument against using TAI in certain applications though? i.e. if you're designing a system which relies on non-leapsecond-updated devices to be in sync with devices that are capable of being updated, you should always design as such to accommodate the former rather than the latter?
  - lmm 8y ago
    No? It's an argument for using TAI,because devices that use TAI do not need leap second updates - a clock running TAI that was started in 1970 and never updated will show the same time as a clock running TAI that's started today (or one that was started in 1970 and updated continually), whereas a clock running UTC that wasn't updated will show a different time from one that was updated.

seniorsassycat 8y ago

> Time zone boundary changes and splits The united states will experience this soon. California and Washington are independently seeking to end daylight saving time. There is no America/Portland or America/Seattle so Washingtonians and Oregonians are using America/Los_Angeles. Unless Washington, California, and Oregon end Daylight savings on the same schedule we'll have to add new id's and users will have to change their configuration, and we'll still have issues described in the link.

deleted 8y ago

[deleted]

kuon 8y ago

I think it depends on what the time represents. When a user inputs a time, let's say for an alarm, this is always "wall clock", and this is what you store. The timezone can be stored too, but this is also dynamic. For example, a user put a conference in a calendar at 7 am, this is dependent of the user location the day of the conference. For app following the user, this is simple (like a mobile app), you just use the current timezone of the system the app is running on, but for a distributed system (client server), it might be complicated to pick a timezone and is dependent of the application. For computer times (like logging, cron...), just use UTC.

barrystaes 8y ago

Dont confuse UTC with GMT. Store absolutely everything in UTC, do all calculations on UTC, and show in whatever timeunit the user wants. The one edge-case where user inputs a local time thats is not unique (repeated hour due to daylight savings time) can easily be solved consistently, and often is not even a problem at all.

em500 8y ago
This is bad advice if you need to store future events/appointments. Because in the majority of cases the meaning of the future datetime is "the date/times on the majority of the calendars and clocks on the wall in the vicinity of the event". The implication is that if the local timezone rules change between now and the future event, the user wants the local datetime of the appointment to stay unchanged, which implies that the UTC/epoch version of the appointment has to be changed. Generating the correct datetimes from UTC/epoch+local timezone requires a lot more administration and logic than storing just the local datetime. (You need at least both the current TZ database and the version when the appointment was made/last changed to have a hope to do this correctly.)
deleted 8y ago
[deleted]
nulbyte 8y ago
That's not the only edge case, and the blog points that out.
philwelch 8y ago
I used to think that, when I was young and naive, which was maybe a month or so ago. Here's a use case: You and I are agreeing to meet at a given landmark. This could be any landmark in the United States, just to keep it simple. The user experience is: you propose that we meet at <landmark> at 4 PM tomorrow, and I agree. This happens on a website. As far as you, me, and most of the system is concerned, the only sensible way to store that time is as a timezone-agnostic, UTC-agnostic local time: "2019-03-28T16:00". That's because, for the system to store that time as UTC, we have to know the UTC offset of every landmark in the United States at any point in time (which isn't an easy problem!) and consistently, correctly convert between UTC and that local time every time it gets displayed anyway. If we screwed up the UTC offset when we stored it the first time, we have to change the UTC time. Now, if there's an additional constraint that you can only schedule meeting times that are actually in the future, you have to know what times constitute "the future" relative to the UTC offset of the landmark, so you don't get out of needing a comprehensive UTC offset map of the United States. But if you screw that up, you just end up putting an irrelevant or impossible meeting time in a dropdown and maybe dealing with some users attempting to time travel. Even that is a failure mode that users will somewhat understand, but most of the time it won't matter unless users are scheduling within the same time window as your UTC error. If you stored it in UTC, you'd be screwing it up all the damn time until you fixed your UTC offsets, at which point previously-working data would be incorrect because someone would schedule a meeting 8 days out in one of those geographic regions for 6 PM and suddenly wonder how it got shifted to 5 PM for no reason. Now, if we're scheduling a meeting online, and videoconferencing instead of traveling to the Navajo Reservation in Arizona--of course we store it in UTC. And if some of us are traveling to the Navajo Reservation[1] and some of us are dialing in from Newfoundland[2], we're all fucked anyway. [1] The Navajo Indian Reservation spans multiple states, including Arizona. Arizona does not observe DST. The Navajo Indian Reservation does. However, inside the Arizona portion of the Navajo reservation, there is a Hopi Indian Reservation, which does not observe DST. Inside of the Hopi reservation, there is another part of the Navajo reservation that does observe DST. [2] Newfoundland's UTC offset isn't an integer number of hours, there's an extra half hour in there. Enjoy!

barrystaes 8y ago

PSA: this article is confusing UTC with GMT.

deleted 8y ago
[deleted]
seniorsassycat 8y ago
The author did not confuse GMT for UTC. I live in Seattle, I set a reminder on my phone one minute before new years 2021. date --date "2021-01-01T07:59+00:00" Thu Dec 31 23:59:00 PST 2020 Currently Washington follows daylight saving time so we'll be using PST on new years eve. Washington lawmakers are considering a bill to end daylight saving time. If it passes then new years eve 2020 will be PDT, not PST 2021-01-01T07:59+00:00 is midnight PST but it is 1:00 AM PDT and my alarm will be off by an hour.

PaulHoule 8y ago

What is more obscure but equally obnoxious is that dates are also affected by time zones. For instance, Emperor Hirohito went to bed on December 8, 1942. A few hours later, bombers attacked Pearl Harbor at December 7 local time.

testplzignore 8y ago
Things get really fun when you are asked to be able to sort these ambiguous dates. Let's say Event A happens in New York (Eastern Time Zone) at 2018-12-08 02:00 UTC, and is recorded as occurring on 2018-12-07 (with no time) local time. Then let's say Event B happens in London (GMT) at 2018-12-08 01:00 UTC, and is recorded as occurring on 2018-12-08 (with no time) local time. Which event happened first? If we know the UTC times, a reasonable person would say Event B happened first. But if we just have the local dates with no times, we either say that Event A happened first, or we say we don't know. This is a real problem I have to deal with and it is a pain. One user says A happened first, another says B happened first, another says A and B happened at the same time, and I say "give me the UTC time or you don't get this feature" :)
- jrochkind1 8y ago
  Then you can talk about Russia's Gregorian calendar switch. The day the Russian revolution began on 25 October 1917, it was 7 November 1917 in England.
  - oftenwrong 8y ago
    $ cal 9 1752 September 1752 Su Mo Tu We Th Fr Sa 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
    - jrochkind1 8y ago
      Not in Russia that wasn't the calendar in September of 1752! Apparently the `cal` command assumes certain geographic areas when it shows you calendars...

reaperducer 8y ago

Time is so messy, and that messiness becomes apparent when the yes/no, true/false world of computers tries to interpret things. The blog posts brings up a lot of good points about time zones changing, and the comments make even better points about people moving across time zones. Something that confounds me occasionally is the concept of "local human time" because there are all sorts of ways that humans tamper with time. Lawmakers, for example, that are required to pass certain laws by a particular date will sometimes unplug clocks in the legislative chambers to stop the legal passage of time while negotiations continue. You end up in a situation where, on paper the next day or week, 134 laws were negotiated, voted on, ratified, re-voted, passed, and signed all at exactly $date:23:59:59. A physical impossibility, but a legal fact. Or, as I explore the more interesting corners of America more, there are entire towns and regions that ignore their official time zone and every house, business, church, pharmacy, etc... is on a more convenient time for the locals. The only place you see the official time is at the post office. People are messy. Time is messy. People * time = a complete mess.

sebazzz 8y ago
> Lawmakers, for example, that are required to pass certain laws by a particular date will sometimes unplug clocks in the legislative chambers to stop the legal passage of time while negotiations continue. You end up in a situation where, on paper the next day or week, 134 laws were negotiated, voted on, ratified, re-voted, passed, and signed all at exactly $date:23:59:59. A physical impossibility, but a legal fact. Is this internationally accepted or a USA-only thing? It seems like fraud to me.
- philwelch 8y ago
  The polite term is "legal fiction".
- PeterisP 8y ago
  I don't know about that particular description, but it kind of makes sense - two bills passed on 27th March parliament session are passed on the same "business day" even if that day gets long and one of them was voted on five minutes before midnight and the other was voted on five minutes past midnight; and if there are some mandated legal delays (e.g. you have x days to before they'll be in force or something) then both bills will be treated as if they were passed on the same date. This also matches common usage - if I tell you what I did on a friday night, it'll include both things before and after midnight. There are also parallels in banking where business days don't align with calendar days, and you may have (depending on regulations and agreements) either a "long friday" or "long monday" where an action that's physically performed on a sunday will be treated with an effective date of friday or monday respectively; or possibly events after the closure of a business day may be legally treated as happened in the day after.

shikoba 8y ago

It's because a date and a time are not the same thing. Storing date as a UTC time is of course always wrong.

edoo 8y ago

If you have ever done anything seriously with time zones you store everything UTC and must use a library to translate. The problem is it is geopolitical, so the time zone conversion result can vary based on the date itself as well as the physical location. For example DST was changed in 2007 in the US, there is no option but to programmatically translate.

jobigoud 8y ago
Even if you store in UTC and use a library you have to know what the user mean when they create a recurring appointment. I currently have several recurring alerts in my calendar app. One of them is an alarm everyday at 10 p.m. This must be in local time everywhere I go. Another is a conference call at 10H30 a.m on Wednesdays, Pacific Time (not my timezone). If you treat them the same way one of them will be wrong when I travel.

sudhirj 8y ago

There was another discussion here 10 months ago after Zach Holman's post: https://news.ycombinator.com/item?id=17181046 https://news.ycombinator.com/item?id=17181046 Sort of a summarization of most of the ideas here is: * for clear instants like a system log UTC makes sense * for times in the past (far enough in the past that you know your current TZ rules are correct) UTC makes sense. * for the immediate past (near enough where your rules might not be correct) you might want to store zone as well. For all cases of times in the past, remember that you're trying to record an instant. An exact unambiguous point in time that is the same irrespective of who is looking at it or where they are. For this UTC makes sense - people who see that instant converted to their local timezone are noting what their clocks would have shown at that instant, but everyone who will ever see it is always referencing the same instant. FUTURE times are very different. There are times at which you want to reference a future instant, at which point you can just use UTC, but most often what you really have is "this is what I expect to see on my wall clock when this thing happens". That is a very different beast, because nobody knows what the timezone rules or geopolitical situation will be when that thing does happen. You need to recognize the use cases where what you mean is " I expect to see XX:YY:ZZ on my wall clock when this happens" and just record that information as is, including as much information as you can about that wall clock - possibly which timezone it (currently) uses, where it is (in case the timezone itself changes) and then convert to an instant at the last possible moment, or keep updating your instant based on changing rules.

IgorPartola 8y ago
There is one thing that I ran into with past times that I think falls outside of this. When you record things like “first day of employment”, you really do mean the date and not the datetime, and the date should be interpreted in whatever the local timezone is. Theoretically your first datetime if employment is what, midnight of the day you show up? Or is it 8am or whatever when you actually first step through the door? Each situation will be interpreted differently by the parties involved so best you can do is to store the date and the location/timezone of where they work, even though it’s in the past.
- sudhirj 8y ago

kazinator 8y ago

I don't see how the countdown timer can fail to reach zero when the conference starts, if we have calculated the correct number of seconds between now and the conference, and do not change the conference time based on whether or not daylight savings time is canceled, to preserve it apparent local time. If we decide that the conference's start may be a moving target based on changing DST rules, then our coutdown timer cannot smoothly count down. At every tick, it has to evaluate the amount of time left to the conference start, and show that. The conference start's local time is always converted to UTC based on the current rules; when the rules change, it jumps by one hour and so the countdown timer adjusts itself at the next tick to show an extra hour. You can't smoothly count-down toward something that starts at X or at X + 1h, and you don't know which until later.

fendy3002 8y ago
Which is why storing the time data in UTC isn't a correct approach, because it just become countdown timer. A user may need to be reminded at specific time in a future, on a specific time zone. And it can't be reached by merely put a countdown timer.
- kazinator 8y ago
  That doesn't follow. If the DST rules change, then, effectively, any event whose local time stays the same has actually moved. The sun will be in a different spot of the sky when that meeting is held than it would have been. All the dates stored in UTC which move due to a DST rule change have to be traversed and updated, as do any dependent countdown timers. That doesn't mean it wasn't "correct" to store them that way. "Correct" means that we handle all the cases in our specifications and design, and they are translated to code without mistakes. People like countdown timers. If there is going to be a countdown timer to an event whose time is uncertain, that countdown timer has to update itself when the exact time is pinned down. Until then, it should probably use the shorter countdown so that it errs on the side of too early. It has to be updated whether the time is stored as local time or as UTC. The behavior of the countdown timer cannot depend on how the time is stored in some database.

ble 8y ago

The distinction between different kinds of time is really pretty key to having a clear understanding. Civil time is different from atomic time and different from sidereal time or solar time. Civil time is how people, businesses, and governments organize and plan their activities. Atomic time is monotonic, physical, and "more objective" / less dependent on legislatures :) Sidereal time is about the orientation of Earth relative to stars. It's really important if you're an astronomer. UTC is a civil time standard based closely on TAI, an atomic time standard, except it gets corrected to be within 1 second of UT1, a sidereal time standard. It is basically _the_ civil time standard and it is a little compromise to make a civil time that advances like atomic time but also does not desync from sidereal time. Different administrative regions use offsets from UTC to determine their local civil time. If you're writing an application where people plan future activities and you really want to "get things right / not surprise users", you probably need to store times in the appropriate civil time standard, make it apparent _which_ civil time standard is canonical per activity if people are being coordinated across administrative regions that do not synchronize their standards, and ensure that your application can find out about planned changes to civil time in any of the administrative regions of interest.

crooked-v 8y ago

The "time zone version" issue mentioned in the article isn't quite as hard as it looks, as time zone-based libraries will (if done right) include the whole history of time zone changes too. For example, java.time and the JS lib moment-timezone both use a full time zone history to convert to/from UTC, not just current offsets.

jonskeet 8y ago
I think you've misunderstood what's changing here. It's not a matter of time zones not knowing that rules vary across time. It's a matter of knowing that "the rules for 2022" vary over time as well - in that the rules I might predict for 2022 may well not be the case now. "Time zone history" (in every platform I've seen) does not mean "knowing what the rules for 2022 were in 2019". They mean "getting things right according to all the currently-known rules, across all of history". That's very different.

amenod 8y ago

Of course. The real advice should be: - store past dates in UTC - store future dates in the format that makes sense, based on what the truth is. Scheduling event at 9AM local time? Use local time. Determining when the spacecrafts meet? Use UTC (or Federation Standard Time if you prefer ;) ).

jrochkind1 8y ago

I believe the problems described here are with future dates, and future timezone definition changes you don't know about 'right now' when you converting a local time to UTC for storage. The nature of the problem is that when you convert from a local timestamp to UTC, you might have been "wrong" in the conversion. Or, more specifically, it may have been as correct today, but it may become wrong some time in between now and when the timestamp occurs, because of future time zone definition changes. A future timezone definition change may mean you should have converted to UTC differently, which you couldn't have known because the timezone definition change happened after you converted. (Usually statutorily; sometimes maybe because your timezone data was wrong and was later corrected?) I don't believe these problems apply to _past_ dates or "right now" timestamps. Unless your timezone data is wrong I guess. Does this seem right?

NateEag 8y ago
I think it's close. As the article explains, though, present and past timestamps can be impacted too - your timezone library may be out of date at the moment you save the timestamp, or in some cases the legal body defining the timezone may reach into the past with a timezone change.
- jrochkind1 8y ago
  How can a legal body possibly change the timezone retroactively? All those people on Monday that _thought_ it was 1pm, it turns on Tuesday we decided it was actually 2pm when they thought it was 1pm. But nobody could have known that on Monday. So they've got to... go back and change any times they put on documents on Monday? How does that make any sense? But incorrect timezone data or implementation is too real, true.
  - NateEag 8y ago
    Governments can do what they like, by and large. Just because it's crazy doesn't mean they won't, and nothing about physical reality prevents them from doing this. I'm not aware of any specific examples, so perhaps I'm being too paranoid in my first post, but it would not surprise me in the least to hear of, for example, a bill to eliminate DST in a state that somehow manages not to pass until a day or three after the DST change takes effect. There have at least been some some bizarre calendar changes that many software systems fail to accommodate, which probably feeds into my above paranoia: https://en.m.wikipedia.org/wiki/Soviet_calendar https://en.m.wikipedia.org/wiki/Soviet_calendar https://mentalfloss.com/article/51370/why-our-calendars-skipped-11-days-1752 https://mentalfloss.com/article/51370/why-our-calendars-skip...

karmakaze 8y ago

An interesting case was time stamps for photos on a global photo sharing site. UTC and conversion to local for display was the normal pattern. We decided to change this to display the time of the photo local to where the photo was taken. This where-taken and when-taken were paired. It made new years' and other holiday or time of day shots make sense.

SonicSoul 8y ago

I think a more correct title would have been "date storage antipatterns" storing in UTC is fine as long as your clients are time zone aware and can convert to local and send timestamps with time zone so that the server can convert back to UTC. the "options" provided are just examples of how not to do it.

mixmastamyk 8y ago

I noticed another use case regarding photos on the filesystem. I like to see them in their local datetimes, rather than the absolute time in my current location, but don't believe linux filesystems supports that. For example if I travel and take photos I don't want their mod times to be of those back home, which can be in the middle of the night or wrong day. Have been moving them to my local timezone via a time shift, but not sure that is the right thing to do. Now they are in my timezone, but if I move one day, will I have to update all my photo times? Even with exiftool and DateTimeOriginal, that's more file updates than I'd like to do.

nybble41 8y ago
The filesystem is doing the right thing here by accurately storing the original point in time when the photo was taken (or at least it was before you shifted it). If two files were modified simultaneously then they should have the same mod times, even if the changes occurred on opposite sides of the planet. The problem is all in the presentation layer; you want your file explorer (or 'ls') to display the times in their original timezone, not the timezone you're currently in. There is an EXIF tag for the time zone offset but support for it appears to be limited. The original timezone could be inferred from the EXIF location data, however, if you have that enabled.

revskill 8y ago

Why not just store both UTC and timezone right into the database ?

FearNotDaniel 8y ago
Because for future events whose time is fixed in their local timezone, you can't be certain that the timezone's offset from UTC on that future date is going to be the same as what you currently think it will be.

z3t4 8y ago

I like unix time. But its not perfect either. Soon we will have a new y2k problem where unix time can no longer fit into an integer. Anyone remember the y2k problem ? People predicted major catastrophes as the year was encoded with two digits. But no one is talking about unix time.

tombert 8y ago
This will certainly be a problem for older apps, but anything I've done in the last 10 years I've been dev'ing, literally every library I've used for Unix time uses a 64 bit integer, which should last for a few million years.
- z3t4 8y ago
  Some programming languages still don't have 64 bit integers!
feedbeef 8y ago
I wonder what kind of fallout will come from the year 2038 problem [1]. It appears the Deep Impact space probe was lost to a 32-bit clock rollover bug, which portends some level of disaster come 2038. 1. https://en.wikipedia.org/wiki/Year_2038_problem https://en.wikipedia.org/wiki/Year_2038_problem

dheera 8y ago

On a related note, I do all of my personal time in UTC. All of my phones, clocks, watches, computers, calendars are set to UTC. It just makes everything easier for my personal projects.

kpmah 8y ago

Am I right in thinking that if you always store in UTC, when the clocks go back you will get overlapping data? e.g. if you store your logs with a UTC date you'll see interleaving logs for the extra hour, or if you read sensor data or something like that you'll get double readings?

simcop2387 8y ago
Well kind of. At least with how it works in the US. The first 2am is PST, the second 2am is PDT. so while they look the same, they're actually different times still. That said, rendering this for a user is still a pain depending on the UI.
Qwertystop 8y ago
No? Or not necessarily, at least. For example, pytz (a Python timezone library) offers "is_dst" flags for exactly that purpose. >>> loc_dt = datetime(2002, 10, 27, 1, 30, 00) >>> est_dt = eastern.localize(loc_dt, is_dst=True) >>> edt_dt = eastern.localize(loc_dt, is_dst=False) >>> print(est_dt.strftime(fmt) + ' / ' + edt_dt.strftime(fmt)) 2002-10-27 01:30:00 EDT-0400 / 2002-10-27 01:30:00 EST-0500 https://pythonhosted.org/pytz/#problems-with-localtime https://pythonhosted.org/pytz/#problems-with-localtime
- kpmah 8y ago
  Yeah, I think I'm confusing storing things as UTC with storing things as UTC+0 - I forgot it stores the offset.

foxhop 8y ago

Store timestamp and convert it to whatever the user wants.

delfinom 8y ago

Why is this even a problem? This is the entire job of tzinfo which any respectable language uses to resolve datetime conversion.

nurettin 8y ago

As in save geographically bound event calendars with local time. Problem solved.

Scarblac 8y ago

In our application we can compare charts of timeseries data that may originate in many different places in the world. If you want to compare e.g. two charts of temperature over a 24-hour period between two places, you want noon for both to be in the same place on the X-axis. So you want to use local time of both places. But if you only store UTC, you don't know local time. No need for tzinfo changes or travelling through time zones.

jchw 8y ago

Storing UTC is still usually right because in many cases rather than dealing with calendar times, developers are dealing with instants in time that have already passed/are passing (creation/update timestamps, logs, etc.) I think this basically is only important when you might store a date in the future. If you have timezone rules incorrect for present or near past dates, well you're going to have a hell of a time dealing with that no matter what you do, really. Handling calendar times and recurring times have their own complications.

EGreg 8y ago

We simply use the google timezone API to find the event’s local timezone, for physical events, then store UTC. For non-physical events, we store UTC based on the timezone of the user agent that made the request, taking into account daylight savings rules. On the extremely rare occasion that rules change between when the event was scheduled and when it takes place, we don’t have a system in place, so the time would have to be updated manually. NOW... it gets even more interesting for recurring events. Does 11AM on Tue mean 11AM on Tue after daylight savings? How about events like Shabbat candle lighting times that depend on when the sun sets? It took us a while to do all these. Does anyone know a good non-API solution to timezone calculations / shapefiles feed?

kazagistar 8y ago

The silver bullet isn't storing UTC, its getting rid of timezones. Why not just have a universal clock and call it good?

zbentley 8y ago
Because people need to tell each other about time-related things while being globally distant. "Call me at dinnertime, say 7." "My 7 or your 7?" Is a lot easier than "Call me at 0400" and having both parties do offset math in their heads.
- chrisseaton 8y ago
  I don't understand. Your first example is the one where people need to do offset maths. The second example requires no offset maths as it's 0400 for both parties and they both already know what they're doing at 0400 if we didn't have timezones.

xellisx 8y ago

https://github.com/evansiroky/timezone-boundary-builder https://github.com/evansiroky/timezone-boundary-builder

clhodapp 8y ago

I don't understand the described result for Option 1 or how having stored in UTC creates a problem there. It would seem to me like when the government changes the rules for local time, they are effectively moving the (real, not clock) time for the conference. It would seem like there is enough information stored in the database that installing the new time zone rules should cause the timer to move to accommodate this, as long as addresses don't get redefined (a different issue with that storage model). I don't see why the timer would not instantaneously jump an hour to correct itself when it becomes aware of the new rules or why it wouldn't hit the zero at the right time.

chaz6 8y ago

I have always thought that time would be best stored as milliseconds from TAI. It is a measure that is universally constant and unambiguous.