0

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                |
+------------------+-----------------------+
Chloe
  • 25,162
  • 40
  • 190
  • 357
  • Related: https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific (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 Jan 22 '19 at 08:28
  • It’s not my home field, but you may consider whether `LocalDate` of java.time and [FasterXML/jackson-modules-java8](https://github.com/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. Jan 22 '19 at 12:49
  • I think you need used LocalDateTime. Here solution for you : https://stackoverflow.com/questions/43476364/hibernate-with-java-8-localdate-localdatetime-in-database – squalltrt Jan 22 '19 at 13:11
  • @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 Jan 22 '19 at 18:51
  • @OleV.V. OK but can you explain why this one is not working? I will need the time as well in the future. – Chloe Jan 22 '19 at 18:56
  • No, I can’t, but I am sure that you are correct that it’s a time zone issue. – Ole V.V. Jan 22 '19 at 19:42
  • That depends on how and where you set `user.timezone=UTC`? Eg setting it in code after the default timezone has already been used has no effect – Mark Rotteveel Jan 23 '19 at 10:00

1 Answers1

0

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.

Chloe
  • 25,162
  • 40
  • 190
  • 357