Storing ZonedDateTime in Postgres in UTC

Question

I receive a variable as ZonedDateTime in UTC and I want to persist in a Postgres column in UTC. The column currently is type of timestamptz and I want to read is back in the same ZonedDateTime format. No conversion is between.

Reading it back converts to my local time zone which I don't want. My question is, is the ZonedDateTime to timestamptz correct?

Answer 1

tl;dr

Writing:

myPreparedStatement.setObject( … , myZonedDateTime.toOffsetDateTime() )

Retrieving:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) 

You cannot get back a ZonedDateTime object, as moments with a time zone are not supported as a data type in Postgres nor in the SQL standard.

Details

The SQL standard

The SQL standard barely mentions some date-time types, without defining much in the way of details and behavior. So databases vary greatly in their approach to date-time handling.

The SQL standard does define the types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE. But those are misnomers, were the standard. authors meant offset-from-UTC, not actual time zones.

JDBC mapping

So the JDBC standard maps TIMESTAMP WITH TIME ZONE values to the java.time.OffsetDateTime type. That type represents a moment as seen with an offset from UTC of some number of hours-minutes-seconds, positive (eastward) or negative (westward).

JDBC does not address either of the java.time.Instant nor java.time.ZonedDateTime classes.

Convert ZonedDateTime to OffsetDateTime

I receive a variable as ZonedDateTime in UTC and I want to persist in a Postgres column in UTC.

Convert your ZonedDateTime to an OffsetDateTime.

OffsetDateTime odt = myZdt.toOffsetDateTime() ;

For example, a date-time this year with Europe/Paris zone assigned will convert to a date-time with an offset of +01:00 or +02:00.

Writing/retrieving in database

The particular offset within your OffsetDateTime object does not matter. When saved to a Postgres column of the type TIMESTAMP WITH TIME ZONE, any supplied offset or zone information is used to adjust the value to an offset of zero hours-minutes-seconds. This adjustment is a Postgres-specific behavior, not specified in the standard. Some other databases do the same, some do not.

Send to database.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

You cannot get back your ZonedDateTime object directly. As discussed above, neither Postgres nor the SQL standard store date-times with a time zone.

If the original time zone is important to you, you must store that separately in another textual column.

myPreparedStatement.setString( … , zdt.getZone().toString() ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
ZoneId zoneId = ZoneId.of( myResultSet.getString( … ) ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( zoneId ) ;

By the way, be aware that time zone names do change on occasion.

Always UTC

Postgres always stores a date-time value in a TIMESTAMP WITH TIME ZONE column with an offset of zero.

JDBC always retrieves a value from a TIMESTAMP WITH TIME ZONE column as a OffsetDateTime object with an offset of zero.

⚠️ Unfortunately, some tooling, middleware, and frameworks choose to inject a default time zone or offset. Between the database and you, the retrieved UTC value may be adjusted. This does not happen with straight JDBC. But beware of frameworks built on top of JDBC; these may choose to inject an offset or zone. While well-intentioned, this anti-feature does cause much confusion.

Use full type names

I suggest you not use the abbreviation timestampz.

For one thing, that term is proprietary to Postgres, not standard.

And for another thing, that term is too easily misread as timestamp. Given how crucial it is to correctly choose TIMESTAMP WITH TIME ZONE versus TIMESTAMP WITHOUT TIME ZONE, I suggest always spelling them out the long way.