If datetime libraries were more thoroughly patterned after that of Java 8, there would be less need for the 'misconceptions programmers believe about datetimes' posts, and less need for the 'just use UTC' or 'maybe always-UTC isn't the right advice' posts, because the data-types are thoroughly reflective of their intended use, and their misuse is harder than in other APIs.
[1] https://docs.oracle.com/javase/8/docs/api/java/time/package-... [2] https://news.ycombinator.com/item?id=12364805
You want a column that can store both timestamp and timezone, so why not just have 2 columns? You already have to convert the timestamp into the user's timezone when rendering because the user's timezone can change. Storing timestamps with timezone just complicates things, for ex:
display_in_user_zone(TIMESTAMP UTC)
display_in_user_zone(TIMESTAMP_WITH_TIMEZONE)
The above is exactly the same, but the UTC one will prevent bugs in the long run.
The only time it's acceptable to store time with timezone is with DATES that do not have a TIME component.
If you really need to display the timestamp in the timezone the user originally entered, store the original timezone along with every timestamp.
It really should have been called "TIMESTAMP IN UTC" or "NORMALIZED TIMESTAMP" or something else instead. It's especially dangerous because the results you get back from using it look correct with casual testing and you might not notice that your database is subtly wrong for a long time.
Let's say I'm writing a calendar application. Naturally, the user wants to enter all events in their own timezone, which in my case is Europe/Amsterdam. So I can just convert that to UTC, and store it along with a timezone string? Well, that's going to break horribly when the EU abolishes summer time next year, because all of those UTC stamps will be wrong!
The only sane option here is to store local time, and a string representing the timezone, which is what the name "timestamp with timezone" suggests it is. Yes, I could do the conversion manually, but it would be a lot more convenient to have it in the database directly.
Personally I find the API confusing and arbitrary. Why include a Year class? Why include both OffsetDateTime and ZonedDateTime? What is the difference between Instant and LocalDateTime for my application that doesn't travel across time zones? Why isn't there an Range class with a start and a stop?
I guess I find "just use UTC" much easier to deal with.
CREATE TYPE datetimetz AS (
dt timestamptz,
tz varchar
);
CREATE TABLE example (
id integer,
created_at datetimetz
);He'd be someone I want on any new RFC effort.
SQL timestamp == java.time.LocalDateTime
SQL timestampz == java.time.Instant, but converted into java.time.OffsetDateTime on read
?
That is, neither of them actually stores a timezone. But timezone is a date and time that needs to be interpreted in the context of some separately-supplied timezone, whereas timezonetz is an actual instant in time?
IME, it's rare to need LocalDateTime. I work in finance. I often need LocalTime, to specify things like "the exchange opens at 07:30 every day", where the timezone is implicitly that of the exchange, and if the exchange moved (lol Brexit), its timezone might change even if its opening time didn't. I also often need LocalDate, mostly when storing or retrieving data in archives organised by date, because we tend to use the local date for that, so that all the data for one session of an exchange, from open to close, is on one date, wherever in the world it is [1]. But i very rarely need LocalDateTime. Perhaps if i worked in calendaring instead, i would use it all the time.
It's a shame that SQL doesn't have a ZonedDateTime or OffsetDateTime, but as others have pointed out, you can just store a timestamp along with a timezone or offset.
[1] Actually, it's worse than this, in that we use the trade date, which is sometimes not the same as the local date - for example, on the Chicago Mercantile Exchange, the market for the E-mini S&P 500 future opens at 17:00 and closes at 16:00 the next day [2]. But that entire session is considered to have a trade date of the calendar date it closes on. In the olden days, the market would open at 07:00 and close at 16:00 (so you had an hour before the banks closed to settle any cash movements, or possibly because traders had very big lunches in those days). Trade date was calendar date. But then the 21st century came along, and they decided to open all day. But because there was so much crucial stuff that happened at the close of trading, they did it by moving the open back to the previous evening, rather than moving the close forward.
Finally, boycott Oracle's table server. It's horrible in this respect.
I think this is spot on, and is the reason why it's so hard to design time libraries, particularly in language standard libraries. How do you allow all users to express what they need, while not requiring any users to express what they do not need?
Bear in mind that Joda/java.time are still oversimplifying, because they take UTC as their ground truth, which means they can't account for leap seconds. A really thorough time library would take TAI as the ground truth, then have a layer which knew about leap seconds to convert to UTC [1], then a layer which knew about timezones to convert to local times. Not knowing about leap seconds means you can't calculate the time between two Instants accurately - if there is a leap second in the interval, the time will be off by one. However, since most people don't mind about gaining or losing a second every now and then, UTC is a decent tradeoff.
> Personally I find the API confusing and arbitrary. Why include a Year class?
So you can't accidentally pass the year and month, which would otherwise both be integers, the wrong way round to a method which takes those, as i did earlier this week.
> Why include both OffsetDateTime and ZonedDateTime?
There's a time format which looks like '1918-11-11T11:00+01:00'. You can parse that to an OffsetDateTime, but you don't have enough information to parse it to a ZonedDateTime. Was that Paris winter time, or Lagos standard time? If you wanted to find the exact six-month anniversary of that moment, you would need to know, because it would be at 11:00 local time [2], which could be 1919-05-11T11:00+02:00 in Paris but 1919-05-11T11:00+01:00 in Lagos, and those are different moments in time.
> What is the difference between Instant and LocalDateTime for my application that doesn't travel across time zones?
If your application, or one of its users, resides in one of the timezones which have summer and winter times, then you still have two offsets to think about. Every Instant identifies a unique moment in time for you (up to leap seconds!), but a LocalDateTime does not, because there is one hour a year of LocalDateTime values which identify two different moments an hour apart, and one hour a year of values which don't identify anything at all.
[1] inb4 someone goes off about relativity
[2] because everyone knows that the armistice was signed at 11 o'clock on the 11th day of the 11th month, so if you rock up and say "bonjour mes amis, le cérémonie de l'anniversaire c'est à 10h" you will get bayonetted
Right now, supposing you have two columns, EventTime TIMESTAMPTZ and EventTimeZone TEXT, you'd have to do something like the following in your query to get the data returned to the application properly:
SELECT (EventTime AT TIME ZONE EventTimeZone) || ' ' || EventTimeZoneWhen you insert into a 'TIMESTAMP', it's as if it did input LocalDateTime(x).atZone(UTC).toInstant(), and saved that; and when you read it, it's as if it does storedInstant.atZone(UTC).toLocalDateTime().
A 'TIMESTAMPTZ' is stored the same way, but on string output is converted to the session timezone, and an underspecified input is enriched with the session timezone.
When you insert into a 'TIMESTAMPTZ' it'd take the input OffsetDateTime or ZonedDateTime, or enrich the input with the session zone until one were obtained, call .toInstant(), and save it. On read, it'd do storedInstant.atZone(sessionZone) to obtain a ZonedDateTime, and its default output format only includes the offset.
LocalDateTime is useful for calendaring, and as an intermediate step when joining a LocalDate and a LocalTime and working towards a Zoned or Instant.