CODEWITHSUNDEEP
javamysqlspringspring-bootjackson
Chloe
Chloe

Reputation: 26264

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

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

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                |
+------------------+-----------------------+

Upvotes: 0

Views: 2432

Answers (1)

Chloe
Chloe

Reputation: 26264

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.

Upvotes: 0

Related Questions