I try to insert a record. I found a problem with When the server's time zone is greater than the time zone set by the database. timestamp will automatically convert the data in that field. As shown in the image I insert this record at 11-10 00:51am ,server time zone is 8

enter image description here

But conversely, when the server's timezone is less than the timezone set by the database, it does not convert

I insert this record at 11-09 21:56pm ,server time zone is 5

enter image description here

I am using java for testing, the orm framework is jpa, and I am using jdbc to connect mysql

Someome could tell me why?

CodePudding user response：

tl;dr

myPreparedStatement.setObject( 
    … , 
    ZonedDateTime
    .of( 
        LocalDate.of( 2021 , Month.NOVEMBER , 10 ) , 
        LocalTime.of( 0 , 51 ) , 
        ZoneId.of( "Australia/Perth" ) 
    )                                                  // Returns a `ZonedDateTime` object.
    .toOffsetDateTime()                                // Returns a `OffsetDateTime` object.
)

… and …

myResultSet.getObject( 
    … , 
    OffsetDateTime.class 
)                                                      // Returns a `OffsetDateTime` object.
.atZoneSameInstant( 
    ZoneId.of( "Australia/Perth" ) 
)                                                      // Returns a `ZonedDateTime` object.

Details

Do not trust your tooling to report true values as retrieved. Some tools have the anti-feature of applying a time zone after retrieving a date-time value from the database. Well-intentioned, but very confusing.

A TIMESTAMP column in MySQL 8 always stores values adjusted to UTC. That means an offset of zero hours-minutes-seconds ahead or behind the UTC meridian. Per the docs:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

Always specify your desired time zone or offset-from-UTC. Never rely implicitly on default time zone within Java, within your database connection, or within your database server.

ZoneId z = ZoneId.of( "Asia/Hong_Kong" ) ;

Always use java.time classes. Never use the old Date, Calendar, SimpleDateFormat classes.

LocalDate ld = LocalDate.of( 2021 , Month.NOVEMBER , 10 ) ;
LocalTime lt = LocalTime.of( 0 , 51 ) ;
ZonedDateTime zdt = ZonedDateTime.of( ld , lt , z ) ;

To send to the database, convert from a time zone to a mere offset via the OffsetDateTime class, to be compatible with SQL.

OffsetDateTime odt = zdt.toOffsetDateTime() ;

If you want to see the value that will be eventually stored in the database, adjust offset to zero.

OffsetDateTime odt = zdt.toOffsetDateTime().withOffsetSameInstant( ZoneOffset.UTC ) ;

Send to database.

myPreparedStatement.setObject( … , odt ) ;

Retrieve from database.

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

Adjust to a time zone.

ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;  // Same moment but viewed through the wall-clock time of a particular time zone.

You mentioned using JPA (now known as Jakarta Persistence). The latest versions of JPA fully support the java.time classes. Tip: To experiment and learn how this all works, skip JPA and do some direct JDBC calls as practice.

All this has been covered many times already on Stack Overflow. Search to learn more.

CodePudding user response：

How is your time_zone parameter on your MySQL server? you can check it with this query:

SELECT @@time_zone