I use a java.time.ZonedDateTime to create a timestamp with time zone information:

2022-11-27T22:32:27.697077 09:00[Asia/Tokyo]

My understanding is that when the JPA Entity is inserted into the database, it is transformed to UTC, since that is what the timezone of the database is configured to.

After the transformation, the value in database is:

2022-11-27 13:32:27.697077 00

Based on this situation:

1. I'm wondering is the [Asia/Tokyo] information lost once data stored in database?
2. How would I derive [Asia/Tokyo] as the original timezone,
3. Or do I need to manually store that information separately myself?

Java Entity:

@Column(name = "zoned_date_time")
private ZonedDateTime zonedDateTime;

Liquibase changeSet:

<databaseChangeLog>
    <changeSet author="me" id="1669077010756-1">
       <addColumn tableName="item">
         <column name="zoned_date_time" type="timestamptz"/>
       </addColumn>
    </changeSet>
</databaseChangeLog>

Output from JVM just before database insertion:

java.time.ZonedDateTime: 2022-11-27T22:32:27.697077 09:00[Asia/Tokyo]

Query inside PostgreSQL Docker container:

select zoned_date_time from item;
->
2022-11-27 13:32:27.697077 00

postgres=# show timezone;
 TimeZone
----------
 Etc/UTC

CodePudding user response：

I'm wondering is the [Asia/Tokyo] information lost once data stored in database?

Yes

How would I derive [Asia/Tokyo] as the original timezone, Or do I need to manually store that information separately myself?

You can convert the UTC date-time to any time zone; so, if you have stored the time zone ID (e.g. Asia/Tokyo), you can use that to restore the original ZonedDateTime as shown below:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT zoned_date_time FROM item");
while (rs.next()) {
    OffsetDateTime odt = rs.getObject(1, OffsetDateTime.class));
    ZonedDateTime zdtOriginal = odt.atZoneSameInstant(ZoneId.of("Asia/Tokyo"));
}
rs.close();
st.close();