wait, why? unix timestamps are safe from this kind of mess as they are UTC seconds since the epoch, all the tz-aware conversion happens when you need to display localized dates but the timestamp you save should be as neutral and tz-unaware as possibile. I've seen way more bugs from people assuming system clock to be UTC while it was localized. Like missing and overwritten data on daylight saving changes.
You lose context. Say you have 2100-01-01T10:00:00 in PST. That's 4102452000, so you store it.
A few decades later, timezones are changed. PST should add 30 minutes, all others stay the same. You only have 4102452000, what should you convert it into? You lost the time zone information, so you can't make the correct decision.
Changes to timezone changes can be quite abrupt too, this one was announced about 3 months before it took effect. Developers scramble to update software.
if you need context, e.g. location, you can still save it in another field and you will be able to correctly display localized time at any point given the universal timestamp and the current location. Or you can save properly localized and tz-aware time, I just find timestamps safer from pitfalls.
> if you need context, e.g. location, you can still save it in another field
No. Say I have a database with a bunch of timestamps for future events. I calculate the time of those events with my current idea of local time (because my customer or because legal requirements require me to do something at a specific local time), convert to GMT, and store as a timestamp in my database.
In 2024, California decides to no longer observe Daylight Savings Time. All of those current timestamps no longer happen at the proper time in the newer version of America/Los_Angeles.
So there's a big pitfall here, in that the relation between localized time and UTC timestamp changes.
Sure, you and others like you make pretty valid points. I believe we are talking about different use cases, hence the confusion. I was thinking of my most common use case of archiving events, usually experimental data, with a non ambiguous time reference.
If you need to schedule future events it gets obviously a lot more complicated, you don't need a timestamp you need local time and you need it to be robust to future changes in timezones, DSTs, politics.
Basically, it's important to recognize what you're actually measuring/capturing and store something maximally equivalent to that. Conversions may render figuring things out later impossible or very difficult.
If you care about a past moment in time as reported by GPS or a computer clock-- use a UTC timestamp.
If you care about a future time delineated by a precise interval from now-- use a UTC timestamp.
If you care about a future time expected to be delineated in a specific time zone, use a timestamp in local time and note the zone.
If you care about a future time in a customer's time zone no matter where they may be, store the time in an unspecified local time and the customer for whom the time applies. Etc.
You seem to make a contradicting statement here with what you wrote in the sibling comment, no? timezone information are influenced by political means, whereas localization (lat/long) information is not. UTC timestamp + localization seem more correct to me.
The semantics of "time X at point Y" are different from "time X in time zone Z". Sometimes the former may indeed be what you want, but it's uncommon for the user to be providing precise location information for a point in time far in the future. If you're told "time X, Eastern standard time", then the semantically correct thing to do is to not guess a point in space, but instead preserve the time zone as provided.
This line of thinking is how we end up with nonsensical UX like having to select "America/Los Angeles" to get US Pacific time. There are many use cases for datetimes and timezones, not all of them need a location and in some cases it's incorrect or misleading to include a location. You could certainly use a UTC timestamp and coordinates in your application but that doesn't work for many other use cases.
Your example is valid and replies are ill-informed. There is a difference between a "date" and "time interval". Timestamps are good for storing intervals, but if you need to plan something on a _date_, you need to store the date. This means you care not about specific number of seconds having passed since now, but about what calendar (and the clock) is saying when the event has to happen.
Example: nobody cares if a concert planned on November 19, 2024 at 19:00 in Honolulu starts in 12345600 seconds or 12349200 seconds since now. But everyone cares that everone's calendars and watches are in sync by that time and show specifically that date and that time, regardless of how many times people switched DST or timezones in the years in-between.
Just store the time stamp and time zone separately and update the time stamp to reflect any changes to a time zone's offset (if that actually happens)...
It takes a bit of housekeeping, but isn't especially difficult.
> Just store the time stamp and time zone separately and update the time stamp to reflect any changes to a time zone's offset
Thus reinventing zoned datetimes badly
> if that actually happens
It happens literally all the time, sometimes with very little heads up e.g. the decision to apply DST or not can be take mere weeks before it actually occurs[0], and something as impactful as an entire calendar day disappearing can happen with 6 months notice[1].
Thanks for this - I've learned something new today. I did not realise timezone updates were so frequent.
I do find that having the timestamp at hand makes math easier in a lot of the use cases I've come across. I wonder if there is an easy hybrid solution where you can capture timezone info, timestamp info and have it adjust without a full DB update, even if timezone offsets change. Maybe a mapping table of some kind, with versioned timezone codes.
As far as i'm aware, you should only store the location next to the timestamp and let the IANA[1] do the maintenance of the tz database. Please avoid the housekeeping on your side.
It actually is especially difficult at scale for non-trivial applications. Large databases won't be able to atomically change every instance which may lead to very frustrating bugs where collisions cannot be allowed. For example, on a calendar app. I agree with dtech, if you're storing in the future don't use a timestamp unless the input value is specifically intended to be timezone unaware, which is typically only useful for short term things like control systems or alarms. For longterm human uses a datetime+timezone field or an ISO datetime+timezone string is safer.
For many purposes the correct specification of a future time is in terms of local time at a particular location on that date. This is true for financial contracts (including trillions of dollars in options markets -- 10am in New York means NY time) but also of work schedules (scheduled to arrive at 9am? That is on the workplace clock, not UTC), local transit schedules, and many other things.
That means when daylight savings time rules change or timezone lines are moved the UTC time of these events change.
Unfortunately I don't believe anyone has standardized a format for storing times which are specified local time at a specified location on a specified date. So it is all roll-your-own or use UTC or zoned datetime and be burned when things change.
The problem is that the contract normally doesn't specify epoch seconds. So if the translation of UTC since epoch to local time changes, the local time specified in the contract does not, so your UTC since epoch timestamp has to.
I don't think so. Imagine you have date saved somewhere at 10am 1 July 2031. If the EU abolishes DST in between, do you want the hour changed to 9am or keep it at 10am? I'd say both could be correct.
You convert it to the local time at that location at that point in history. That's something that belongs to time localization, timestamp should be universal and immutable against those changes.
So I'd say you are arguing against using timestamp representations for any dates far in the future since they can change depending on what happens with timezones and DST, but timestamps cannot.
My point is that either you save a properly localized tz-aware time in unambiguous standard format (iso 8601?) or you save a universal timestamp (and location if needed) and defer all the localization process to the moment you need to display localized time.
From my experience the latter is safer because people tend to save datetime strings in a format that's either not standard nor unambiguous.
I don't think you're answering the question here though. It's completely plausible there are situations where the contant is local time, not UTC (or TAI, if you want to get really obnoxious). Let's say I made an appointment to get my hair done in Berlin 10 years in advance, at 14:00 local time 29 Dec 2031, for whatever reason. Let's say someone goes crazy and moves Berlin to +1:30 this decade. I would still expect to show up at 14:00 local, but that's not the same universal time it was 10 years ago. How do you represent that in your DB where everything is in UTC?
Frankly, time doesn't work that way...
if i am atm (CET) and i make an appointment for sometime in summer 2022 (CEST), then i don't expect the hair salon to make the appointment in CET.
Honestly I'm not sure, in ten years Berlin timezone could not exist anymore, in your example you need a special time representation that says "this time in local time, whatever local time will mean in the future". I'm not sure we have that in current date time representation standards, do we? you could still save tz-unaware local time and a location, and figure out in the future what local time means at that point of time and space.
I’ve worked on systems where data has a 100-126 year retention objective. We had the “fun” of dealing with this looking backward as far as 1968!
When we reimplemented the system from an ancient mainframe, we stored the value as a Unix timestamp and a standard representation of local time. It was derived from an old format going backwards and recorded in the new format going forward.
Having both is critical for our successors to figure out wtf is going on. UTC 1/1/70 is an anchor point, and the local representation is canonical at a point in time. I don’t recall how 1968-1970 was handled. Not only do you have to plan for “Berlin time” going away, but for daylight (or double daylight) times changing. Having both entries allows you to reconcile, so the poor bastard figuring out what to purge in 2090 has a fighting chance!
