OffsetDateTime value is changed when retrieved from the database

Question

I am using a table from MS SQL database and developed a GET API using Spring Boot, to retrieve all the data from that table.
Here the table contains a column of type datetimeoffset(7).

I have data member OffsetDateTime updatedDate inside my Entity, used to map datetimeoffset column from the DB

Now, when I get the API response, I see the updatedDate value (2021-06-17T05:37:40.1938687+05:30) is different from actual database value (2021-06-16 19:07:40.1938687 -05:00).

I explored web to find some help and tried few solutions similar to Solution, however I did not see any positive results.

Any help would be really appreciable, thanks!

PS. Spring Boot version: 2.3.1.RELEASE

score 1 · Answer 1 · answered Jul 10 '21 at 17:01

1

These two values represent exactly the same point at timeline. They are just represented in different time zones: database uses EST and Spring app uses IST Indian. When we convert them to GMT they will look the same:

String[] dates = {"2021-06-17T05:37:40.1938687 +05:30", "2021-06-16T19:07:40.1938687 -05:00"};
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.n XXX")
        .withZone(ZoneId.of("GMT"));
Arrays.stream(dates)
        .map(date -> formatter.format(OffsetDateTime.parse(date, formatter)))
        .forEach(System.out::println);

Above code prints:

2021-06-17T00:07:40.1938687 Z
2021-06-17T00:07:40.1938687 Z

If you want to keep original representation you need to instruct to use EST timezone instead of default or system one. Try to set: spring.jackson.time-zone property in configuration file:

Time zone used when formatting dates. For instance, "America/Los_Angeles" or "GMT+10".

answered Jul 10 '21 at 17:01

Michał Ziober

37,175
18
99
146

Thanks for the response. I have added the spring.jackson.time-zone property in application.properties with value as GMT-5, however I still see the same result. Do I need to do any other additional jackson related configuation apart from the post https://stackoverflow.com/questions/46653455/date-timezone-with-spring-boot-and-jackson/46653797#46653797 – Vishal Jul 11 '21 at 14:22
@Vishal, Which version of `Jackson` do you use? Did you create your own instance of `ObjectMapper` or you use common one created by `Spring`? How does it work when you create a test controller and return new instance of `OffsetDateTime`? – Michał Ziober Jul 11 '21 at 14:29
I am using ```Jackson``` version as ```2.11.0``` and yes I have provided customised instance of ObjectMapper where in I am explicitly disabling ```WRITE_DATES_AS_TIMESTAMPS``` and ```ADJUST_DATES_TO_CONTEXT_TIME_ZONE``` When I return a new instance of ```OffsetDateTime```, I see its having ```IST``` zone – Vishal Jul 11 '21 at 14:36
Hi Michal, btw when I send ```OffsetDateTime``` instance with different ZoneOffset, I see the new zone in API response correctly. For example, ```OffsetDateTime now = OffsetDateTime.now(); now = now.withOffsetSameInstant(ZoneOddset.of("-05:30"); return now;``` I need jackson to do this for me. – Vishal Jul 11 '21 at 14:43
@Vishal, try also to check how `OffsetDateTime` looks like when it is loaded from database. Do you use `Hibernate` or any other `ORM`? Take a look at [Hibernate not correctly storing ZonedDateTime as datetimeoffset in SQL Server](https://stackoverflow.com/questions/35808423/hibernate-not-correctly-storing-zoneddatetime-as-datetimeoffset-in-sql-server) – Michał Ziober Jul 11 '21 at 17:38
even after trying above, I still on the same page – Vishal Jul 13 '21 at 17:27

score 1 · Answer 2 · answered Jul 10 '21 at 17:27

1

Now, when I get the API response, I see the updatedDate value (2021-06-17T05:37:40.1938687+05:30) is different from actual database value (2021-06-16 19:07:40.1938687 -05:00).

No, both the Date-Time represent the same instant/moment.

Demo:

import java.time.Instant;

public class Main {
    public static void main(String[] args) {
        System.out.println(Instant.parse("2021-06-17T05:37:40.1938687+05:30"));
        System.out.println(Instant.parse("2021-06-16T19:07:40.1938687-05:00"));
    }
}

Output:

2021-06-17T00:07:40.193868700Z
2021-06-17T00:07:40.193868700Z

ONLINE DEMO

You can convert one into another

Using OffsetDateTime#withOffsetSameInstant, you can convert one into another.

Demo:

import java.time.OffsetDateTime;
import java.time.ZoneOffset;

public class Main {
    public static void main(String[] args) {
        OffsetDateTime odtFromResponse = OffsetDateTime.parse("2021-06-17T05:37:40.1938687+05:30");

        OffsetDateTime odtDesired = odtFromResponse.withOffsetSameInstant(ZoneOffset.of("-05:00"));
        System.out.println(odtDesired);
    }
}

Output:

2021-06-16T19:07:40.193868700-05:00

ONLINE DEMO

Learn more about the modern Date-Time API from Trail: Date Time.

answered Jul 10 '21 at 17:27

Arvind Kumar Avinash

71,965
6
74
110

Yes, I can understand the dates are exact equal, just the timezone is different. I do not want jackson to convert date from EST to IST, for which I did set the configuration (mentioned in post https://stackoverflow.com/questions/46653455/date-timezone-with-spring-boot-and-jackson/46653797#46653797). However I still see the date retrieved contains IST time zone, not EST – Vishal Jul 11 '21 at 14:27
@Vishal - Try `@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="uuuu-MM-dd'T'HH:mm:ss.SSSSSSSSSXXX", timezone="America/Chicago") OffsetDateTime updatedDate` and let me know if it solved your problem. Change the timezone as per your DB server's timezone. – Arvind Kumar Avinash Jul 11 '21 at 14:44
Hi Arvind, after doing suggested changes, I still see the same timezone. Do I need to stop providing custom implementation of ObjectMapper? JFYI, ```OffsetDateTime``` member is inside ```@Entity``` where I have used ```@JsonFormat``` annotation. – Vishal Jul 11 '21 at 14:51
@Vishal - From your other comment I noticed that you have already implemented a custom `ObjectMapper`. By any chance, did you try the suggestion in [this comment](https://stackoverflow.com/questions/60451939/jackson-offsetdatetime-serialization-z-instead-of-0000-timezone#comment110001441_60454450)? – Arvind Kumar Avinash Jul 11 '21 at 15:05
Hi Arvind, I have already tried this but no luck. One solution to my problem is, to make JVM timezone and DB timezone the same For instance, using JVM argument ```-Duser.timezone=US/Central```. This helps to solve this issue. However, in my case I do not want to make Spring Boot application dependent on timezone of the Database. – Vishal Jul 11 '21 at 15:19

OffsetDateTime value is changed when retrieved from the database

2 Answers2

You can convert one into another