How do I prevent Hibernate from converting ZonedDateTime to UTC when persisting to PostgreSQL `timestamp with time zone`

Question

Setup:

Hibernate: 5.4.32
SpringBoot: 2.5.4
org.postgresql:postgresql: 42.2.23

I have a class and table

@Entity
public class MyType{

    @Id
    @GeneratedValue(generator = "uuid2")
    @Type(type = "uuid-char")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    private UUID id;

    @Column
    public ZonedDateTime time;

    public MyType(){}

    // omitting getters and setters
}

create table if not exists my_type
(
    id text not null
        constraint idx_16559_pk__data_fil__3213e83ff1283b3a
            primary key,
    time timestamp with time zone
)

Problem

What I want to achieve is to preserve the timezone information in MyType.time when writing to the database. No matter what I try it is always converted to UTC. There is an option (hibernate.jdbc.time_zone) to set a specific timezone but I want to persist ZonedDateTime instances with different timezones and since Java and Postgres have a type for this I would expect some way to achieve this.

Worst case scenario for me would be to just store it as a string and do de-/serialization manually.

bvdb · Accepted Answer · 2021-10-22 13:20:41Z

3

In Postgres a TIMESTAMP WITH TIME ZONE actually stores its dates in UTC format in the database.

The "with time zone" means that it expects a timezone to be specified during insertion, and it will convert the specified time to UTC. By doing so, it normalizes all timestamps, which makes sense if you want to compare or sort dates.

So, the database won't store the timezone, and you'll need another way to store or determine it.

All timezone-aware dates and times are stored internally in UTC. (PostgreSQL documentation)

You could regard a timezone more like a kind of "formatting" for your user. Each user can have its own timezone, and the same date should then just be formatted in a different way. It's still the same value, it's just displayed in a different way.

From that regard, it probably makes sense to just store a timezone in the settings or profile of your user.

In my opinion the formatting of this should not even be done by an API. It should be done by the front-end. If the user doesn't have a timezone set in his user preferences, you could in fact use the timezone of the operating system of the client or the timezone of the webbrowser.

edited Oct 22, 2021 at 13:20

answered Oct 22, 2021 at 13:03

bvdb

25.2k11 gold badges123 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Trinova Over a year ago

Just to make sure: The with time zone modifier is just an input/parsing filter and the information itself is destroyed?

bvdb Over a year ago

@Trinova exactly. In a way, it is almost like '10.0' is normalized to '10'. It regards timezone as a kind of formatting, not as real data.

Basil Bourque Over a year ago

“format” and “formatting” are not the right words to use here, as date-times involve formats when generating/parsing text. So your use of that wording here may prove confusing. Otherwise, correct Answer, well written. I se the word “adjustment”, but there might be better wording.

Collectives™ on Stack Overflow

How do I prevent Hibernate from converting ZonedDateTime to UTC when persisting to PostgreSQL `timestamp with time zone`

Setup:

Problem

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Setup:

Problem

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related