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