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 forspring.datasource.url
inconfig/application.properties
user.timezone=UTC
inconfig/application.properties
SET @@global.time_zone = '+00:00';
in MySQLmvn 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 |
+------------------+-----------------------+
java.sql.Date
,java.sql.Time
andjava.sql.Timestamp
are handled in the default JVM timezone). – Mark RotteveelLocalDate
of java.time and FasterXML/jackson-modules-java8 may be the good solution for you. ALocalDate
has got neither time of day nor time zone, so gone will be your problems… – Ole V.V.user.timezone=UTC
. Is that not how to force the JVM time zone? – Chloe