CODEWITHSUNDEEP
spring-boothibernatespring-data-jpajava-timezoneddatetime
user10772849
user10772849

Reputation:

ZonedDateTime to MySql -- Wrong Offset?

Setup: Spring Boot '2.4.1' application, (hibernate 5.4.25), Java 11

Question How do i save a Date (German-Time) with java.time* so that runtime = db

Problem:

During Debug: ZonedDateTime = 2021-05-03 16:11:42.021236

In DB: Datetime(6) 2021-05-03 14:11:42.021236

@Entity
public class User {
  private ZonedDateTime createdAt;

//getter&setter

//calling the setter like like: 
user.setCreatedAt(ZonedDateTime.now(ZoneId.of("Europe/Berlin")))

//saving via JPARepo...
Repository.save(user);

application.properties:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

#tried but no effect
hibernate.jdbc.time_zone=CET

#tried but no effect
hibernate.jdbc.time_zone=UTC

gradle

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-actuator'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-security'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'org.springframework.security:spring-security-test'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'

Upvotes: 0

Views: 1452

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 339382

I do not use Spring nor Hibernate, so I cannot help there specifically. And you have omitted crucial details such as the exact data type of your database column, so no specific answer is possible. But I can give you some general pointers.

Use OffsetDateTime with ZoneOffset.UTC to communicate moments in JDBC

Regarding ZonedDateTime, know that the JDBC 4.2 spec requires support for OffsetDateTime. Oddly enough, the spec omits to require support for the more commonly used Instant and ZonedDateTime classes. This design choice by the Java team befuddles me, especially with regard to Instant which is trivially simple to convert back-and-forth.

➥ Anyways, focus on using OffsetDateTime for your JDBC work.

Generally best to do all your business logic, data exchange, data storage, logging, and debugging in UTC. That is, with an offset from UTC of zero hours-minutes-seconds, and no time zone. Use a time zone only where required by a business rule or the expectation of the user in presentation.

The basic class for moments as seen in UTC is Instant. Generally, this should be the type of your class member fields that track a moment, a specific point on the timeline.

Capture the current moment using Instant.now. To record when the "user" object is created:

Instant userCreated = Instant.now() ;

To record a moment in the database, your column must be of a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE. In MySQL 8, that means TIMESTAMP, not DATETIME. See the documentation.

To write our Instant object’s value to the database, your JDBC driver may be able to handle the Instant object despite being optional in the JDBC spec. If not, or if you want to write more portable code, convert to OffsetDateTime. Specify the constant of ZoneOffset.UTC for an offset of zero hours-minutes-seconds.

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

Pass that object to your prepared statement.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

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

Easily convert back to a simple Instant.

Instant instant = odt.toInstant() ;

To adjust to Germany time, apply a ZoneId to get a ZonedDateTime.

ZoneId z = ZoneId.of( "Europe/Berlin" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Understand that MySQL stores all TIMESTAMP values in UTC, only UTC. Unfortunately, some middleware and tooling may choose to impose time zone adjustments to the values you retrieve from your database. Using the code above, I believe you will not have that problem.

Upvotes: 3

Related Questions