How do I prevent Hibernate from converting ZonedDateTime to UTC when persisting to PostgreSQL `times-CodePudding

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.

CodePudding user response：

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.