Timestamp's datetime string is shown in local timezone instead of UTC on select

Question

My entity has a java.sql.Timestamp field:

@Column(name = "expires_at")
private Timestamp expiresAt;

The table also has a default column updated_at, which is dynamically set on insertions/updates (i.e it is not set through JPA):

  updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

I set expiresAt in the persisted entity this way:

long currTimestamp = Instant.now().getEpochSecond();
LocalDateTime ldt = LocalDateTime.ofInstant(Instant.ofEpochSecond(currTimestamp), ZoneId.of("UTC"));
Timestamp expiresAt = Timestamp.valueOf(ldt);

myEntity.setExpiresAt(expiresAt);

I am located in PDT timezone. I run Spring Boot app locally, and it uses MySQL container in my Docker Desktop. This query shows UTC time, which means that MySQL container's timezone is UTC:

select now();

But when I persist my entity into the table, and do select, the datetime string of expires_at column shows PDT time. And updated_at column shows UTC time.

Interestingly, the records persisted from my AWS ECS Spring Boot app into AWS RDS MySQL instance, show UTC for both expires_at and updated_at.

When I update local JDBC string (for connecting to Docker Desktop container) to mysql://localhost:3306/mydbname?connectionTimeZone=UTC, it shows UTC for both.

Can anyone explain why this happens? And why in AWS RDS they are both shown in UTC even though I am not using connectionTimeZone flag there

Answer 1

You’ve got a mess there.

First of all, stop using java.sql.Timestamp. That terribly flawed legacy class was years ago supplanted by the modern java.time classes defined in JSR 310. JDBC 4.2+ supports java.time. Jakarta Persistence (JPA) and Hibernate have both been updated to support java.time.

Never use LocalDateTime when handling moments, specific points on the timeline. That type lacks the context of a time zone or offset.

Write your code in such a way so that your MySQL container's timezone is irrelevant.

Your database column must be of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE. In MySQL, tha means the type TIMESTAMP. You have used the wrong type on your column, DATETIME. Read the documentation to learn how that type lacks the context of a time zone or offset. That DATETIME type stores only a date with a time of day, so it is inherently ambiguous.

JDBC maps OffsetDateTime class to the SQL standard type TIMESTAMP WITH TIME ZONE.

OffsetDateTime odt = OffsetDateTime.now( Offset.UTC ) ;
myPreparedStatement.setObject( "updated_at" , odt ) ;

Retrieval:

OffsetDateTime odt = myResultSet.getObject( "updated_at" , OffsetDateTime.class ) ;

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

Answer 2

updated_at is simple to understand. Since its value is defined by MySQL , it is always equal to the MySQL timezone which is UTC in both of your local MySQL and AWS RDS.

expires_at is trickier as its value is managed by the JDBC driver which also has its timezone setting. When using

preparedStatement.setTimestamp(index, timestamp)

which hibernate is default to use to configure which value of the time will be stored to DB , the JDBC driver will convert it based on its timezone setting which can be defined explicitly by connectionTimeZone in the JDBC connection URL. Otherwise , it will default to use JVM timezone. So think that your spring-boot app running in AWS is configured as UTC timezone now.

Different situations for expires_at can be explained as follows :

• Run in local + no connectionTimeZone configured ➜ JDBC driver default to JVM timezone (i.e PDT) ➜ stores in PDT time
• Run in local + connectionTimeZone=UTC ➜ JDBC driver convert it to UTC ➜ stores in UTC time
• Run in AWS + no connectionTimeZone configured ➜ JDBC driver default to JVM timezone (i.e UTC) ➜ stores in UTC time

As MySQL DATETIME will not store any timezone information , it is up to the client application that fetch its value to define the timezone. If you do not want to changes its data type , recommend you to at least align your JVM and DB to be in the same timezone setting to prevent some weird problem. I guess now in your local setup you will experience a time shift problem for updated_at when hibernate fetches its value because it will treat it as a PDT time but actually it is a UTC time.