CODEWITHSUNDEEP
javaspring-bootjacksontimezoneoffsetdatetime
Vishal
Vishal

Reputation: 724

OffsetDateTime value is changed when retrieved from the database

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

Upvotes: 2

Views: 1989

Answers (2)

Arvind Kumar Avinash
Arvind Kumar Avinash

Reputation: 79620

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.

Upvotes: 1

Michał Ziober
Michał Ziober

Reputation: 38720

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".

Upvotes: 1

Related Questions