1

I want save date with UTC time zone in sql server 2014. I have used ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneId.of("UTC")) to get the current date time and persisting this to db using Hibernate session object. While debugging I can see that in Java program date is fine like this 2019-09-25T13:22:29.573Z[UTC], but after saving in database column it is something like this 2019-09-25 18:53:23.3630000. It's automatically converting the time part according to system time. Can anyone please suggest what is the issue? I have used datetime2 datatype while creating this column in database.

i.bondarenko
  • 3,442
  • 3
  • 11
  • 21
Mopendra Kumar
  • 71
  • 1
  • 12
  • Sql server isn't storing the time zone as part of the date so how would you expect it to be stored if not in the time zone for the system time? Modify the server to use UTC time if you need the exact same time or remember to convert it back to UTC when reading the date back to java – Joakim Danielson Sep 25 '19 at 13:28
  • what do you get if retrieving the date with hibernate/java and printing it by java (or in debugger)? – user85421 Sep 25 '19 at 13:32

1 Answers1

5

Wrong type

You are using the wrong data type for your column.

The type datetime2 cannot represent a moment. That type stores only a date and a time-of-day, but lacks the context of a time zone or offset-from-UTC. So if you store “noon on the 23rd of January in 2020”, we cannot know if you meant noon in Tokyo Japan, noon in Tunis Tunisia, or noon in Toledo Ohio US, three different moments several hours apart. This wrong type is akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE. The equivalent class in Java is LocalDateTime.

If you already have data stored, you will need to refactor your database. Basically, add a new column of the correct type, copy data over for each row, converting as you go. Of course, this only works if you know the intended time zone that was absent from each value saved.

Right type

While I don’t use Microsoft SQL Server, according to the doc you should be using a column of type datetimeoffset to record a moment. This type adjusts any submitted value into UTC for queries and sorting, while also recording the offset. This type is akin to the SQL standard type TIMESTAMP WITH TIME ZONE.

Generally best to store your moments in UTC, an offset of zero hours-minutes-seconds.

The Instant class represents a moment in UTC. 

Instant instant = Instant.now() ;

If you have a ZonedDateTime, you can extract an Instant. 

Instant instant = zdt.toInstant() ;

We would like to save that Instant directly to the database. Unfortunately the JDBC 4.2 soec does not require support for either of the two most commonly used java.time classes: Instant and ZonedDateTime. The spec does require support for OffsetDateTime. So we convert. 

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Submit to database. 

myPreparedStatement.setObject( … , odt ) ;

Retrieve.

OffsetDateTime odt = MyResultSet.getObject( … , OffsetDateTime.class ) ;

Extract an Instant to make clear in your code that you want UTC. 

Instant instant = odt.toInstant() ;

See this moment through the wall-clock-time used by the people of a particular region (a time zone).

ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

Table of date-time types in Java (both legacy and modern) and in standard SQL.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Hi Basil, Thanks for your response actually I am using Hibernate for persist and retrieve data on that case which class I can use for this date-time field in my pojo class? – Mopendra Kumar Oct 04 '19 at 10:26