Timestamps and Time Zones in PostgreSQL (2016)

I frequently wish that datetime libraries and APIs frozen in time and awkward design were given a second pass and redone in the style of Java 8 [1], which is a triumph of clarity after many years of lessons learned. Even putting out a new RFC would help move things along, because I've long felt that RFC 3339 is underspecified [2].

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

I don't know. I don't necessarily think it's a bad wish but I also feel that the API has its' downsides. My impression is that it has caused a fair amount of confusion among Java developers because it forces a more complicated understanding of time upon people who in many cases aren't interested in writing applications that deal with describing events accurately across centuries or with multiple time zones.

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.

In case anyone isn't aware, Stephen Colebourne was the specification lead for JSR-310 which eventually became java.time.* He had previously written Joda-Time an open source library which prior to the release of Java 8 was the de facto date/time library for Java for about a decade.

He'd be someone I want on any new RFC effort.

Am i right in thinking that in Java 8 terms:

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.

[2] https://www.cmegroup.com/trading-hours.html#equityIndex

> My impression is that it has caused a fair amount of confusion among Java developers because it forces a more complicated understanding of time upon people who in many cases aren't interested in writing applications that deal with describing events accurately across centuries or with multiple time zones.

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

PostgreSQL 'TIMESTAMP', for nonextreme values, is the same idea as java.time.Instant -- an absolute point on the timeline stored internally as a number from some epoch -- but whose default textual interface accepts and emits java.time.LocalDateTime values that correspond, but aren't tagged with the UTC zone.

When 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.