Aside from issues of synchronization, leap seconds, data container limitations and such, it's important to choose the correct kind of time to store, and the right kind depends on what the purpose of recording the time is.
There are three main kinds of time:
*Absolute Time*
Absolute time is a time that is fixed relative to UTC (or relative to an offset from UTC). It is not affected by daylight savings time, nor will it ever change if an area's time zone changes for political reasons. Absolute time is best recorded in the UTC time zone, and is mostly useful for events in the past (because the time zone is now fixed at the time of the event, so it probably no longer matters what specific time zone was in effect).
*Fixed Time*
Fixed time is a time that is fixed to a particular place, and that place has a time zone associated with it (but the time zone might change for political reasons in the future,for example with daylight savings). If the venue changes, only the time zone data needs to be updated. An example would be an appointment in London this coming October 12th at 10:30.
*Floating Time*
Floating (or local) time is always relative to the time zone of the observer. If you travel and change time zones, floating time changes zones with you. If you and another observer are in different time zones and observe the same floating time value, the absolute times you calculate will be different. An example would be your 8:00 morning workout.
*When to Use Each Kind*
Use whichever kind of time most succinctly and completely handles your time needs. Don't depend on time zone information as a proxy for a location; that's depending on a side effect, which is always brittle. Always store location information separately if it's important.
Examples:
Recording an event: Absolute
Log entries: Absolute
An appointment: Fixed
Your daily schedule: Floating
Deadlines: Usually fixed time, but possibly absolute time.
Timestamps are so complicated once you factor in timezones and daylight savings that it doesn't belong in JSON. Time zones are not static. They can change from country to country, or even states within countries. Ditto for when daylight savings is enacted during the year - even changing over the years. There is no rhyme or reason to any of this. The data for this has to be stored in tables and time zone meanings can change retroactively. The only reliable time stamp is UTC without leap seconds. (Speaking of leap seconds, who thought seconds going from 0 - 60 rather than 0 - 59 was a good idea?)
Accurate time is one of the most difficult things to model in computer science.
Which is fine for past events, but doesn't solve the issue of future or repeating events. There are actually three main kinds of time: absolute time, fixed time, and floating time [1]. Storing everything in UTC only solves for situations involving absolute time.
That makes sense, but really we are not going to be able to guarantee full accuracy of a timestamp against future changes in timezone boundaries. I guess if you had a GPS coordinate, that could be done, but it would be a monumental task for little benefit. Which is why IMO time data should be stored in unix, because then we can at least guarantee that we have an accurate ordering of events, at least within the precision of the timestamp.
Would rather convert to unix at the time the event is recorded since we will be able to convert to the unix standard from whatever locale. If we store timestamps with a locale then we have to worry about the exact type of locale changes that you mentioned, and there is no guarantee that we will even know how to translate the locale that was stored at the time the TS was created. Sure, unix could change too (e.g. leap seconds), but I would be much more confident that time libraries will handle such changes
Unix timestamp (actual reference with easy math) + timezone give you absolute time to work with, such that you can work out the correct presentation for the timezone even if the timezone has undergone multiple revisions in the intervening years.
There's still a bunch of problems (timezone of event origin? Event reporting origin? Server? Client? Client + presentational overlay?) and confusing things to work through (including relativistic problems) but the consistency of the reference point is valuable. Nobody is going to confuse what timezone you are using as your point of reference when you use Unix epoch. Just use a 64-bit number to hold it.
Unqualified absolute statements (like this one!) are always wrong.
It's not possible to make a generalized assertion about the correct way to deal with time. If you're building a global distributed database, you probably want all your timestamps in UTC. If you're building a calendar application, not so much.
Some systems also need to record the time the observation was made, introducing a 2nd dimension of time. This becomes important very quickly when trying to design a system with an immutable (append-only) data store.
I tend to agree that dates and times are awful to work with but storing everything as a Unix timestamp still seems to be the easiest way to avoid most of the headaches, in my experience. there's nothing worse than trying to reconcile a database filled with naive dates with no associated time zone information. is it daylight savings? is it in this timezone or did they enter it while traveling and their computer automatically updated the time?
The simplest way to minimize these issues, in my experience, is to put the logic for converting local time into UTC in the program and only store the Unix stamps in the DB.
You still have the fundamental issue of storing it correctly in the first place. Do most languages time libraries account for localized temporal shifts of this nature by default? It does seem true that you could query an authoritative source, but on entry:
If I say the event happened 60 years ago exactly, and it’s 1pm, what do I mean by “exactly?” Do I mean the day 60 years ago, at 1pm? Or 60 years ago, taking into account timezone shifts? Do I, the person entering this information, even know or care about this distinction? If you enter an event as 60 years ago, would your average joe expect that the time would shift by 23 minutes, or would they think there’s something wrong with the software?
What about timezone differences that vary depending not only on geography, but also on the ethnicity of the people associated with the event?
I think summary here is that is 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).
>For internationalisation storing in UTC is still the gold standard, since it remains consistent with itself across time and DST zones.
Saving as UTC doesn't really solve the issue (although it seems like it does).
The issue that the previous posters (Khao, laut) didn't make explicit is that we have 2 concepts of datetimes:
1) scientific datetime (which UTC is unambiguous, except for obscure things such as leap seconds)
2) socially-constructed datetime (which "local" time + TZ resolves most (but not all) of the ambiguities)
Scientific(UTC) datetime works great for past events like server logs, and some types of future events such as celestial events. For example, the upcoming solar eclipse in August 21 2017. Regardless of what future DST rules are decreed by the government, the moon is going to move in front of the sun at a 16:48:33 UTC time.
Socially-constructed future datetime such as appointments don't work the same way. For example, we presume that a Super Bowl on the east coast in the year 2025 will have a kick off of 6:30pm but we can't encode that as UTC unambigously because we don't know today what DST rules will be in effect 10 years from now. In 2016, we don't know whether to encode it as 22:30UTC or 23:30UTC.
That said, if the planning horizon is short (such as sending out alerts for an upcoming conference call to participants spanning timezones), one can encode it as UTC internally and then decode it to local wall-clock time. It may be simpler that way with less bugs.
> 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.
It is every bit as painful as it is made out to be.
A common problem that I don't see discussed much is the difference between recording when a time took place (just record and store it in UTC!) and then translating that into what the user expects to see. This is especially hard if you have information that should be grouped into "days", and a use case where the user changes time zones but may expect the same "relative to originally recorded time" to be shown when viewing past events.
Yes, storing the local time and the timezone is not exactly what's enough, all that time I was thinking about the PostgreSQL timestamp with time zone type, which is preferable to use vs storing the Unix timestamp.
I guess we've been talking past each other, with me being the main culpable :)
One thing I try to hammer home is that the time zone portion of a time is absolutely NOT to be used for any other purpose than to determine the time value itself. The moment you need that kind of data for other purposes, you should be recording a separate field in addition to the time field.
Why do you prefer UTC? I feel storing plain unix times is simplest since there's no time zone information at all. And there's no possibility of forgetting a conversion, since (with most languages/libraries) different types are used for plain unix times vs. local times.
> That only applies if what you're storing identifies a point in time.
A meeting is definitely "a point in time", and your example illustrates my point: the display of "2022-07-01 09:00 UTC+2" should be up to the client (which has a timezone setting) but the storage of this meeting's time should, in my opinion, not be "2022-07-01 09:00 UTC+2" but the timezone-neutral "1656658800".
Since there is technically no such thing as "Berlin time", it should be up to the client to decide whether the date you selected in the future (July 1, 2022) is UTC+1 (winter) or UTC+2 (summer) and then store the time in a neutral way to ensure it's then displayed correctly in other clients that have another timezone set (for example the crazy Indian UTC+5:30).
Meetings are a good example of when this matters because it's important that meetings happen at the same time for everyone, which will always happen when the source data is unix epoch. Of course it also happens with correct time zone conversions, but to me that adds unnecessary complexity. Other examples given, like the opening hours of a store, are probably better to store in a "relative" way (09:00, Europe/Berlin) as the second part of that tuple will vary between UTC+1 and UTC+2.
Time is one of those libs that you don't try to implement on your own. It's one of those things that sound simple on the face of it, but you soon find out there's a huge number of complications. Things like locales having different daylight savings, which require a DB to keep track of, and might change in the future. Historically baked in weirdness like the thing in this SO answer. The international dateline. Arizona having its own TZ. Half hour countries. Cultural specific calendars like Jewish or Arab. It's a cave of horrors.
At best, try to understand that if you do use something other than epoch time, you might have to record a bunch of stuff like what I mentioned.
That's exactly the point, but the other way around.
If the timestamp is defined in local time (e.g. "a meeting is scheduled for 15:00 every second thursday") and you generate a list of the next events as UTC, and then the DST rules change, you have to go in and fix all the UTC timestamps for future events.
If you store local time + timezone name, the stored time doesn't have to change, and if you need it in UTC for display you can calculate it on the fly, using the most recent rules you know.
If intervals are more important (e.g. do something every 30 minutes, or exactly every 24 hours), then UTC is better, or if you are recording events as they happen.
That is usually enough when recording events that happened at a specific point in time. Even then you sometimes want to record the offset in the user's timezone, so you know the relevant local time (e.g. when taking a photo).
But it's not enough for things agreed to happen in a specific time zone (meetings, contracts, subscription billing, etc.) In those cases you have to record the local time and timezone name (plus disambiguation for DST switching) and figure out how to handle changes of the timezone definition (e.g. by updating denormalized UTC representations or offsets).
These are all good points and I have thought about it a lot.. It seems that all these issues arise from people wanting to schedule or track a future date that is itself tentative and ambiguous; at the mercy of policy changes.
Ignoring the case where you want to allow a user to track some tentative future date that can resolve to different absolute times there is another truth there: You recorded a future absolute time which now maps to a different local time due to your government mucking about. What about the people in Hamburg who were supposed to join the meeting but missed it because the absolute time shifted?
For usability sake I do absolutely see where you are coming from and the library is surely nice. But I'll stick with storing and mathing UTC unless I absolutely need to start taking these scheduling issues into account :)
Time is something that developers get wrong more often than any other type. Your time data requirements change as your use case for the time changes.
From https://github.com/kstenerud/compact-time/blob/master/compac...
Aside from issues of synchronization, leap seconds, data container limitations and such, it's important to choose the correct kind of time to store, and the right kind depends on what the purpose of recording the time is.
There are three main kinds of time:
*Absolute Time*
Absolute time is a time that is fixed relative to UTC (or relative to an offset from UTC). It is not affected by daylight savings time, nor will it ever change if an area's time zone changes for political reasons. Absolute time is best recorded in the UTC time zone, and is mostly useful for events in the past (because the time zone is now fixed at the time of the event, so it probably no longer matters what specific time zone was in effect).
*Fixed Time*
Fixed time is a time that is fixed to a particular place, and that place has a time zone associated with it (but the time zone might change for political reasons in the future,for example with daylight savings). If the venue changes, only the time zone data needs to be updated. An example would be an appointment in London this coming October 12th at 10:30.
*Floating Time*
Floating (or local) time is always relative to the time zone of the observer. If you travel and change time zones, floating time changes zones with you. If you and another observer are in different time zones and observe the same floating time value, the absolute times you calculate will be different. An example would be your 8:00 morning workout.
*When to Use Each Kind*
Use whichever kind of time most succinctly and completely handles your time needs. Don't depend on time zone information as a proxy for a location; that's depending on a side effect, which is always brittle. Always store location information separately if it's important.
Examples:
Recording an event: Absolute
Log entries: Absolute
An appointment: Fixed
Your daily schedule: Floating
Deadlines: Usually fixed time, but possibly absolute time.
reply