How do I get my Spring Boot API to return UTC times for MySQL Date values instead of “2018-08-01T04:00:00Z”?

Question

How do I get Spring Boot, Java, MyBatis, Jackson, and MySQL to return a UTC time for a date without converting it? The query returns a date like

+------------+
| hitDate    |
+------------+
| 2018-04-24 |
+------------+

The API class has a field like

@JsonFormat(pattern="yyyy-MM-dd'T'HH:mm:ss'Z'", timezone="UTC")
Date hitDate;

API calls return values like

"hitDate":"2018-08-01T04:00:00Z"

Obviously it cannot have a time component. So it thinks the date with 00:00 time is in EST (the system time zone -4:00) and tries to convert it to UTC by adding 4 hours. It works in production where everything is set to UTC. My local system time zone is set to Eastern Standard Time. I tried

&useLegacyDatetimeCode=false&serverTimezone=UTC in the MySQL connection for spring.datasource.url in config/application.properties
user.timezone=UTC in config/application.properties
SET @@global.time_zone = '+00:00'; in MySQL
mvn spring-boot:run -Dexec.args="-Duser.timezone=UTC"

My datasource upon initialization:

DATASOURCE = org.apache.tomcat.jdbc.pool.DataSource@67c7bbdf{ConnectionPool[defaultAutoCommit=null; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=com.mysql.jdbc.Driver; maxActive=100; maxIdle=100; minIdle=10; initialSize=10; maxWait=30000; testOnBorrow=true; testOnReturn=false; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000; testWhileIdle=false; testOnConnect=false; password=********; url=jdbc:mysql://localhost:3306/appdb?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=UTC; username=root; validationQuery=/* ping */ SELECT 1; validationQueryTimeout=-1; validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true; removeAbandoned=false; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null; initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true; abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null; suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false; useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false; ignoreExceptionOnPreLoad=false; useStatementFacade=true; }

My MySQL time zone settings:

mysql> SHOW VARIABLES LIKE '%zone%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| system_time_zone | Eastern Standard Time |
| time_zone        | +00:00                |
+------------------+-----------------------+

Related: stackoverflow.com/questions/14070572/… (that is: JDBC requires that by default java.sql.Date, java.sql.Time and java.sql.Timestamp are handled in the default JVM timezone). — Mark Rotteveel
It’s not my home field, but you may consider whether LocalDate of java.time and FasterXML/jackson-modules-java8 may be the good solution for you. A LocalDate has got neither time of day nor time zone, so gone will be your problems… — Ole V.V.
I think you need used LocalDateTime. Here solution for you : stackoverflow.com/questions/43476364/… — squalltrt
@MarkRotteveel OK but I set the JVM time zone to UTC as well with user.timezone=UTC. Is that not how to force the JVM time zone? — Chloe
@OleV.V. OK but can you explain why this one is not working? I will need the time as well in the future. — Chloe

Chloe Chloe · Accepted Answer · 2019-01-23T23:11:19

Setting the JVM time zone to UTC was sufficient

mvn spring-boot:run -Drun.jvmArguments="-Duser.timezone=UTC"

This works for Spring Boot v1. It is a different argument for v2. Using java.time.*, MySQL connection parameters, or setting MySQL variables was unnecessary. Using LocalDate will give an error com.fasterxml.jackson.databind.JsonMappingException: Unsupported field: HourOfDay because of the JSON Jackson annotation using timestamps. Instant does work though.

I added

@SpringBootApplication
public class App extends SpringBootServletInitializer {
    @PostConstruct
    public void init(){
      TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
    }

For closer DEV/PROD parity, and in case production accidentally gets a time zone set.

How do I get my Spring Boot API to return UTC times for MySQL Date values instead of “2018-08-01T04:00:00Z”?

1 Answers