How to store OffsetDateTime to PostgreSQL "timestamp with time zone" column

Question

I am trying to store OffsetDateTime("2019-01-14 21:10:00.02+03") with timezone(+03) using JDBC to PostgreSQL. But when retrieve data using sql query I always get the +00 result. Is there any way to store offset (+03) with datetime in postgres ?

Tamis

Duplicate of Dealing with DateTime and Timezones with Java / Postgres – Firstrate

Answer 1

Is there any way to store offset (+03) with datetime in postgres ?

Yes, but as @Andreas says, the offset will have to be in a separate column. The column type name timestamp with time zone in PostgreSQL is a bit misleading; it is really just timestamp converted to utc. The original offset is not stored with the timestamp value.

Now you might be tempted to add a new varchar column and save the offset as a hh:mm string, e.g.,

id  twtz                    twtz_offset
--  ----------------------  -----------
 1  2019-01-15 07:00:00+00  -07:00

but in fact it would be better to extract the offset in seconds using ZoneOffset#getTotalSeconds() and store that in an integer column, e.g.,

id  twtz                    twtz_offset
--  ----------------------  -----------
 1  2019-01-15 07:00:00+00       -25200

because that number can be applied directly to a UTC value to convert back to the original OffsetDateTime without having to translate the offset from String to int

OffsetDateTime odtRetrieved = rs.getObject("twtz", OffsetDateTime.class);  // always UTC for PostgreSQL
// odtRetrieved is 2019-01-15T07:00Z
OffsetDateTime odtOriginal = odtRetrieved
        .toInstant()
        .atOffset(ZoneOffset.ofTotalSeconds(rs.getInt("twtz_offset")));
// odtOriginal is 2019-01-15T00:00-07:00

and it also offers some extra robustness in case you ever find yourself dealing with an oddball offset that is in fact not a whole minute.

(If you think that will never happen then keep an eye out for Jon Skeet's upcoming tech/horror novel "Time Zone Rules Almost Broke My Brain". Based on a true story. In bookstores soon.)

Fossiliferous

Answer 2

Postgres stores everything in UTC and does not keep original time zone.

with timezone just means that the value will be converted to the session time zone for you, which is meaningless in the context of JDBC, so you always get the value in UTC.

If you need to store the timezone offset, then you need to store that in a separate column yourself.

Firstrate

Thank you @Andreas, I will storing it in separate column ((( – Tamis