Basically when you tried to purchase a ticket, their frontend would take the date-of-flight, and adjust it by the user's timezone. So a ticket for 2015-01-30 00:00 would become 2015-01-29 20:00, or whatever. The dates in the request did not match what was expected for your booking code, and 500 error.
As for how it breaks, you have a booking for 8pm the day after the start of summer time. You stored the date/time in UTC. Government changes the start of summer time. Your database now thinks it's at 7pm because it uses UTC.
Here's some examples:
https://codeofmatt.com/on-the-timing-of-time-zone-changes/
Also the EU is seriously debating getting rid of clock changes, and it comes up in the UK semi-regularly.
Your database will not fix your bad data when that happens.
I work on a registration and reservation system for rowing boats in my rowing club https://github.com/dsroklub/roprotokol
The only time that matters is whatever the clock in the clubhouse shows (Danish time) If you sign out a boat from the UK (or more realistically from a laptop that thinks it is in the UK) at 14:00 it is still 14:00 Danish time. This is surprisingly difficult to achieve with JS Date object, browsers and popular JS components.
So the Temporal Plain time looks promising.
And especially the explicit timezones. Because even without timezone you need to handle DST. For example my system needs to know that if you start a 3 hour trip at midnight a certain day in the spring, you are expected back at 04:00.
I just hope that browsers and components will support is properly.
* Your meeting is scheduled for 2pm tomorrow.
* Oh oh! Your meeting got bumped to the next day.
* Daylight savings switched overnight.
Some things are measured in relative time. Some things are measured in absolute time. Never confuse the two in your database.
Because it’s not bad data, HaHaOnlySerious. I tell developers that if the database attribute accepts a value it must be in the domain of allowable values. Often people refuse check constraints in order to avoid expressing business logic in the database.
If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. Storing the time zone abbreviation (e.g., EST) is inadvisable, as computers sometimes care whether you asked for EST vs EDT. Storing the time offset (e.g., -500) is incorrect, as it has the same pitfalls as storing UTC - you're precomputing the locale's expected time offset at storage time, and your data won't automatically be corrected if time regulations change.
If you're storing historical timestamps, UTC is fine because you can safely convert it to whatever time zone you want to display, knowing that changes to time zone / DST regulations tend not to affect the past.
At this point in the process first normal form flies out the window. Trying to generalize too much can lead you down some weird garden paths. If it looks like you need a function to validate an prospective column value then you probably need to model the value as a relation corresponding to the function parameters. Then you can make it into a foreign key constraint and get on with your project.
I truly appreciate the efforts of those who attempt to expand the utility of datetime value representation to perfect a wider variety of denotational semantics. But with a relational model it may be better to delegate to simpler abstraction sufficient to the specific case.
It's usecase dependent so it seems like the application should deal with this, at which point persisting in UTC is still a reasonable choice.
If you store this in UTC and the timezone changes, you end up converting to the wrong time.
So for future events you should store local time with a location you can map to a timezone.
But if you want to have precise date time far in the future you must not use UTC based milliseconds representations unless you want to check for necessary database migrations every time the time zone definitions change.
My point in mentioning the solstice is that while some events are at “2pm”, others are “when something happens”.
Sure, UTC is not suitable for conducting astronomy. It is suitable for working with human friendly times, since it is the foundation of timezones.
(1) The time in the local timezone (+ the timezone itself) (2) The UTC conversion (3) The version of the IANA database used to compute (2)
Then you can use (2) the UTC time for most computations/queries, but if a timezone changes then you can use (3) to tell which UTC dates need to be updated, and (1) to recompute the new correct version of (2).
No mucking around with IANA databases, everything's easy to program, server usually keeps itself up-to-date.
"Always use UTC" works if you really, really always use UTC.
Never use UTC for dates. Like OP says, a simple YYYYMMDD string is better.
Temporal is very well designed thanks to building on years of prior art. Give it a try and you'll realize that it leads you to ask those questions the right way.
Seems like everyone has to stub their own toe on this to get it.
Except, there is the cost intrinsic to this discussion, where going back to strings makes sense. That doesn't mean that decision comes with no cost. If you store someone's birthday as "1990-02-05", then need to answer the question "who has birthdays in the next month", that's actually essentially impossible to do in practically any database query engine. You have the problem of both ignoring the year field of that string (ok, so, maybe we just store "02-05" because the year doesn't matter for birthdays, moreso just 'dates of birth', we can denormalize)... but even beyond that, the closest most if not all engines could get is generating an array of every upcoming day in application code (02-05, 02-06, 02-07, etc) then matching on each. Alternatively, just sort on the field (sans year) then filter in application code, but that's also not happening in the database, which has negatives, and moreover, this would suddenly start failing across year boundaries, but you can special-case that by running a second query starting at 01-01 if the first one didn't return enough results...
There's no easy solution. Time is just really hard. Its good to have as many tools as possible in your belt, but with that you have to know when to reach for one versus another. There isn't one solution to any of these problems.
You'll have to do a CHECK on the YYYY-MM-DD string anyway to protect your database from bad data, so why not just do it on the native type?
The native type is likely to be more compact, performant, consistent across the database (with strings, some columns may be YYYY-MM-DD, some may be YYYY.MM.DD etc.) and more readily convertible to the client's type system.
One of the earliest axioms we drill into programmers is "use UTC, use date-time types". Don't store dates as strings, use your database's DateTime type.
If your database has a Date sans-Time type, use that. But many databases don't, and that's where the axiom starts breaking down, because its very natural to say "well, I'll use the next best thing, a DateTime type". But this is wrong! Its far, far better to simply use a YYYY-MM-DD string. I cannot stress how much better this is, but its not obvious why its better to someone who has had that axiom drilled into their heads, and has never ran into this situation before.