Enhancement of ZonedDatetime for SQL

[cbornet]

Currently our support of ZonedDatetime in SQL is minimal : we store the instant represented by this ZD and assume UTC timezone. We have something a little better on Cassandra where a ZoneDatetimeCodec will store the date and timezone in distinct columns and so the timezone is preserved. I propose to do the same for SQL. I did a PoC with the following code to add a column in DB to store the zone id.

    @Transient
    private ZonedDateTime myDate;

    @JsonIgnore
    @Column(name = "my_date")
    private LocalDateTime myDateLocalDateTime;

    @JsonIgnore
    @Column(name = "my_date_zoneid")
    private ZoneId myDateZoneId;

    @SuppressWarnings("unused")
    private void setMyDateLocalDateTime(LocalDateTime myDateLocalDateTime) {
        this.myDateLocalDateTime = myDateLocalDateTime;
        this.myDate = null;
    }

    @SuppressWarnings("unused")
    private void setMyDateZoneId(ZoneId myDateZoneId) {
        this.myDateZoneId = myDateZoneId;
        this.myDate = null;
    }

    @JsonFormat(with = JsonFormat.Feature.WRITE_DATES_WITH_ZONE_ID)
    public ZonedDateTime getMyDate() {
        if (myDate == null && myDateLocalDateTime != null && myDateZoneId != null) {
            myDate = ZonedDateTime.of(myDateLocalDateTime, myDateZoneId);
        }
        return myDate;
    }

    public void setMyDate(ZonedDateTime myDate) {
        this.myDate = myDate;
        this.myDateLocalDateTime = myDate.toLocalDateTime();
        this.myDateZoneId = myDate.getZone();
    }

NB : the behavior when using the REST API depends on Jackson's ADJUST_DATES_TO_CONTEXT_TIME_ZONE setting.

WDYT ?

[cbornet]

Better : I found this little gem of a library which makes it as simple as

    @Type(type = ZonedDateTimeType.NAME)
    @Columns(columns = {
        @Column(name = "my_date"),
        @Column(name = "my_date_zoneid")
    })
    @JsonFormat(with = JsonFormat.Feature.WRITE_DATES_WITH_ZONE_ID)
    private ZonedDateTime myDate;

without the need to add new fields

[pascalgrimaud]

you're the boss in this domain @cbornet : I'm trusting you here :)

[avdev4j]

It's look great, a bit weird to think that no "out of the box" solution exists for every DB but well... Just a question: could we just keep the "myDate" setter? I wonder if the others are useful (setMyDateZoneId and myDateLocalDateTime).

[cbornet]

Just a question: could we just keep the "myDate" setter?

Yes that setter will still be here. I just didn't show it for simplicity.

I wonder if the others are useful (setMyDateZoneId and myDateLocalDateTime).

With the first solution they were needed to reset the ZonedDatetime. But with the second solution the LocalDateTime. and ZoneId fields don't exist so the corresponding getters/setters won't be present either.

[mraible]

@cbornet Any updates on this issue? Can we close it?

[cbornet]

I've got something almost ready. Just need some time to finish it.

[funder7]

Hi, I'm making some considerations about the timezone topic and I've found this issue open, so instead of opening a new one I'd like to ask you...does make sense to set liquibase like this: <column name="myDateTime" type="timestamp with time zone"/> ? This allows to store the time with timezone. The fact is that I'm lazy and I don't want to add a new column for timezone for each datetime field :-) I see many people talking about that, it makes me feel like I'm missing something in my considerations about this topic.

[cbornet]

No. The liquibase type timestamp with time zone corresponds to a time offset (+01:00, +02:00, etc...), not a zone id (PDT, CET, etc...) which takes DST in consideration. If you're only interested in the time offset, you can use an OffsetDateTime instead.

[funder7]

In fact I'm only interested into having time saved correctly, I'm using Instant class

[github-actions[bot]]

This issue is stale because it has been open 30 days with no activity. Our core developers tend to be more verbose on denying. If there is no negative comment, possibly this feature will be accepted. We are accepting PRs :smiley:. Comment or this will be closed in 7 days

[DanielFran]

@cbornet Are you still planning to implement it?

[DanielFran]

@cbornet do you think it is important to add this for v8?

[mshima]

Hibernate 6 has different storage options for zoned times https://thorben-janssen.com/hibernate-6-offsetdatetime-and-zoneddatetime/. I don't think we should serialize timestamps as json, maybe add optional support to TimeZoneStorageType.COLUMN (adjust liquibase generator to add the timezone column).

[dennismzia]

this project looks stale is it still alive

[mraible]

Yes, it's very alive!

[cbornet]

Sorry I forgot about this issue. I’ll have a look again.