This is the 9th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021
Question:
Javaweb project queries always return an incorrect time, 8 hours later than the actual time.
For example, the database records the time as:2019- 11- 21 15:52:30The queried time is:2019- 11- 21 23:52:30By a mile8hoursCopy the codeThe time zone configured in the URL for JDBC connection is invalid.
jdbc:mysql://172.xx.xx.xx:3306/test? useUnicode=true&characterEncoding=gbk&useSSL=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverT imezone=UTCCopy the codeAnalysis:
== analysis: the time zone of the database is GMT + 8 (or GMT + 8 when storing data), while the JavaWeb project is UTC (Universal Time, Universal Standard Time, international Coordinated Time), which should be converted by Java or mysql after query. == Possible parameters such as GMT, GMT8, UTC+8, and UTC0800 were attempted. /usr/share/zoneinfo = /usr/share/zoneinfo = /usr/share/zoneinfo
lewis@lewis-dzwww:/usr/share/Zoneinfo $ll Total usage308
drwxr-xr-x 21 root root 4096 5month27 12:54 ./
drwxr-xr-x 334 root root 12288 6month21 15:11./
drwxr-xr-x 2 root root 4096 5month27 12:56 Africa/
drwxr-xr-x 6 root root 4096 5month27 12:56 America/
drwxr-xr-x 2 root root 4096 5month27 12:56 Antarctica/
drwxr-xr-x 2 root root 4096 5month27 12:56 Arctic/
drwxr-xr-x 2 root root 4096 5month27 12:56 Asia/
drwxr-xr-x 2 root root 4096 5month27 12:56 Atlantic/
drwxr-xr-x 2 root root 4096 5month27 12:56 Australia/
drwxr-xr-x 2 root root 4096 5month27 12:56 Brazil/
drwxr-xr-x 2 root root 4096 5month27 12:56 Canada/
-rw-r-- R -- 1 root root 2102 4月 21 02:09 CET
drwxr-xr-x 2 root root 4096 5month27 12:56 Chile/
-rw-r--r-- 1 root root 2294 4月 21 02:09 CST6CDT
-rw-r--r-- 1 root root 2437 4月 21 02:09 Cuba
-rw-r--r-- 1 root root 1876 4月 21 02:09 EET
-rw-r-- R -- 1 root root 1972 4月 21 02:09 Egypt
-rw-r-- R -- 1 root root 3559 4月 21 02:09 Eire
-rw-r--r-- 1 root root 127 4月 21 02:09 EST
-rw-r--r-- 1 root root 2294 4月 21 02:09 EST5EDT
drwxr-xr-x 2 root root 4096 5month27 12:56 Etc/
drwxr-xr-x 2 root root 4096 5month27 12:56 Europe/
-rw-r--r-- 1 root root 264 4月 21 02:09 Factory
-rw-r--r-- 1 root root 3687 4月 21 02:09 GB
lrwxrwxrwx 1 root root 2 5month27 09:07 GB-Eire -> GB
-rw-r--r-- 1 root root 127 April 21 02:09 GMT
lrwxrwxrwx 1 root root 3 5month27 09:07 GMT0 -> GMT
lrwxrwxrwx 1 root root 3 5month27 09:07 GMT0 -> GMT
lrwxrwxrwx 1 root root 3 5month27 09:07 GMT+0 -> GMT
lrwxrwxrwx 1 root root 3 5month27 09:07 Greenwich -> GMT
-rw-r--r-- 1 root root 1189 4月 21 02:09 Hongkong
-rw-r--r-- 1 root root 128 4月 21 02:09 HST
-rw-r--r-- 1 root root 1190 4月 21 02:09 Iceland
drwxr-xr-x 2 root root 4096 5month27 12:56 Indian/
-rw-r--r-- 1 root root 1678 4月 21 02:09 Iran
-rw-r--r-- 1 root root 4475 2月 24 17:01 iso3166.tab
-rw-r--r-- 1 root root 2265 4月 21 02:09 Israel
-rw-r--r-- 1 root root 507 4月 21 02:09 Jamaica
-rw-r--r-- 1 root root 3 月 21 02:09 Japan
-rw-r--r-- 1 root root 237 4月 21 02:09 Kwajalein
-rw-r--r-- 1 root root 10384 2月 22 23:58 Leap-seconds. list
-rw-r--r-- 1 root root 655 4月 21 02:09 Libya
lrwxrwxrwx 1 root root 14 5month27 09:07 localtime -> /etc/localtime
-rw-r--r-- 1 root root 2102 4月 21 02:09 MET
drwxr-xr-x 2 root root 4096 5month27 12:56 Mexico/
-rw-r--r-- 1 root root 127 4月 21 02:09 MST
-rw-r--r-- 1 root root 2294 4月 21 02:09 MST7MDT
-rw-r--r-- 1 root root 2453 4月 21 02:09 Navajo
-rw-r--r-- 1 root root 2460 4月 21 02:09 NZ
-rw-r--r-- 1 root root 2057 4月 21 02:09 NZ-CHAT
drwxr-xr-x 2 root root 4096 5month27 12:56 Pacific/
-rw-r-- R -- 1 root root 2705 4月 21 02:09 Poland
-rw-r--r-- 1 root root 3453 4月 21 02:09 Portugal
drwxr-xr-x 19 root root 4096 5month27 12:56 posix/
-rw-r--r-- 1 root root 3545 4月 21 02:09 POSIxrules
-rw-r--r-- 1 root root 414 4月 21 02:09 PRC
-rw-r--r-- 1 root root 2294 4月 21 02:09 PST8PDT
drwxr-xr-x 19 root root 4096 5month27 12:56 right/
-rw-r--r-- 1 root root 800 4月 21 02:09 ROC
-rw-r--r-- 1 root root 571 4月 21 02:09 ROK
-rw-r-- R -- 1 root root 428 4月 21 02:09 Singapore
drwxr-xr-x 2 root root 4096 5month27 12:56 SystemV/
-rw-r-- R -- 1 root root 2747 4月 21 02:09 Turkey
-rw-r--r-- 1 root root 127 4月 21 02:09 UCT
lrwxrwxrwx 1 root root 4 5month27 09:07 Universal -> Zulu
drwxr-xr-x 2 root root 4096 5month27 12:56 US/
lrwxrwxrwx 1 root root 4 5month27 09:07 UTC -> Zulu
-rw-r-- R -- 1 root root 1873 April 21 02:09 WET
-rw-r--r-- 1 root root 1528 4月 21 02:09 W-su
-rw-r--r-- 1 root root 17533 4月 13 16:01 zone1970.tab
-rw-r--r-- 1 root root 19014 4月 13 16:01 zone.tab
-rw-r-- R -- 1 root root 127 4月 21 02:09
Copy the codeSolution:
To solve this problem, you can select Hongkong, Asia/Shanghai or Asia/Hongkong in east 8 as the parameter and change the URL to:
jdbc:mysql://172.xx.xx.xx:3306/test? useUnicode=true&characterEncoding=gbk&useSSL=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverT imezone=HongkongCopy the codeTime back is fine.
Conclusion:
UTC and
- Keep mysql database time consistent with the time zone javaWeb connects to mysql. For example, if the database uses GMT time, javaWeb projects use GMT time and store data using GMT time
- If the database connection (the project’s database URL) uses GMT and the database uses GMT+8, 8 hours are automatically subtracted for storage and 8 hours are automatically added for read
Such as:
The time now is:2019- 11- 21 18:30:32The database storage time is:2019- 11- 21 10:30:32The time read from the re-query is:2019- 11- 21 18:30:32Note that the console prints the insertsqlThe log time is:2019- 11- 21 18:30:32So I guess mysql automatically subtracts when storing8Hours, and automatically added when reading8The GMT time is used for the database connection (the project's database URL) and the GMT time is used for the database+8The time ofCopy the code- If GMT+8 is used for database connections and GMT+8 is used for database connections, the storage is directly stored
Such as:
The time now is:2019- 11- 21 18:30:32The database storage time is:2019- 11- 21 18:30:32The time read from the re-query is:2019- 11- 21 18:30:32Console printed insertsqlThe log time is:2019- 11- 21 18:30:32
Copy the code