0. Mysql的TimeStamp召娜、DateTime
1）TimeStamp
- 時間范圍：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
- 以整數(shù)格式存儲运褪，代表自epoch后的秒數(shù)。
- 存儲大小為4個字節(jié)（不考慮小數(shù)部分）玖瘸，即4*8=32位秸讹，除去最高位的符號位，所能表達的最大數(shù)為2^31=2,147,483,648雅倒；
2）DateTime
- 時間范圍：'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
- 存儲占用5個字節(jié)
1 bit sign (1= non-negative, 0= negative)
17 bits year*13+month (year 0-9999, month 0-12)
5 bits day (0-31)
5 bits hour (0-23)
6 bits minute (0-59)
6 bits second (0-59)
---------------------------
40 bits = 5 bytes //來源：mysql官網(wǎng)
3）時區(qū)對DateTime和TimeStamp的影響
以下為mysql官網(wǎng)11.2.2的測試案例
說明：unix_timestamp是當前時區(qū)相對于1970年的秒數(shù)璃诀；
mysql> CREATE TABLE ts (
-> id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> col TIMESTAMP NOT NULL
-> ) AUTO_INCREMENT = 1;
mysql> CREATE TABLE dt (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> col DATETIME NOT NULL
-> ) AUTO_INCREMENT = 1;
mysql> SET @@time_zone = 'SYSTEM';
mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
-> ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = '+00:00';
mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
-> ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = 'SYSTEM';
mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
-> ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = '+00:00';
mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
-> ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = 'SYSTEM';
mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EST |
+--------------------+
mysql> SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 1577891410 |
| 2019-12-31 23:40:10 | 1577853610 |
| 2020-01-01 13:10:10 | 1577902210 |
| 2020-01-01 10:10:10 | 1577891410 |
| 2020-01-01 04:40:10 | 1577871610 |
| 2020-01-01 18:10:10 | 1577920210 |
+---------------------+---------------------+
mysql> SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 1577891410 |
| 2019-12-31 23:40:10 | 1577853610 |
| 2020-01-01 13:10:10 | 1577902210 |
| 2020-01-01 05:10:10 | 1577873410 |
| 2019-12-31 23:40:10 | 1577853610 |
| 2020-01-01 13:10:10 | 1577902210 |
+---------------------+---------------------+
解釋說明：
a）若不指定時區(qū)偏移
- DateTime與時區(qū)無關(guān)，存儲和查詢的操作不會做時區(qū)轉(zhuǎn)換蔑匣，即查詢的值等于存儲時的字符串表示的時間
- TimeStamp與時區(qū)有關(guān)劣欢，存儲時根據(jù)當前時區(qū)轉(zhuǎn)為相對GMT的時間戳，查詢時根據(jù)當前時區(qū)解析時間戳為當前時區(qū)的時間殖演。
b）若指定時區(qū)偏移（since mysql 8.0.19）
- DateTime存儲時把指定時區(qū)的Date Time轉(zhuǎn)為當前時區(qū)的Date和Time氧秘，然后存儲，查詢時原樣取出趴久，不做時區(qū)轉(zhuǎn)換丸相。即只在存儲時轉(zhuǎn)換。
例如：設(shè)置當前時區(qū)為System(ESG, -05:00)彼棍，insert時指定DateTime為2020-01-01 10:10:10+05:30灭忠，根據(jù)時區(qū)的東加西減算法，需要減去10:30, 得到2019-12-31 23:40:10-05:00座硕。存儲2019-12-31 23:40:10 - TimeStamp存儲時根據(jù)指定的時區(qū)轉(zhuǎn)為相對GMT的時間戳弛作，查詢時根據(jù)當前時區(qū)解析時間戳為當前時區(qū)的時間。
1. MysqlWorkbench/Navicat
1）數(shù)據(jù)庫客戶端可以通過命令設(shè)置session時區(qū)华匾；
2. JDBC
1）JDBC的三個配置項：useLegacyDatetimeCode映琳，useTimezone，serverTimezone
useLegacyDatetimeCode: (驅(qū)動8.0已廢棄)
Default: true
Since: 5.1.6
Use code for DATE/TIME/DATETIME/TIMESTAMP handling in result sets and statements that consistently handles time zone conversions from client to server and back again, or use the legacy code for these datatypes that has been in the driver for backwards-compatibility? Setting this property to 'false' voids the effects of "useTimezone," "useJDBCCompliantTimezoneShift," "useGmtMillisForDatetimes," and "useFastDateParsing."
useTimezone: (驅(qū)動8.0已廢棄)
Default: false
Since: 3.0.2
Convert time/date types between client and server time zones (true/false, defaults to 'false')? This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true."
serverTimezone：
Since version: 3.0.2
Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone
2）JDBC調(diào)解時區(qū)
以timestamp為例，如果數(shù)據(jù)源的url時區(qū)配置為serverTimezone=GMT萨西，則會對讀取到的timestamp進行時區(qū)轉(zhuǎn)換有鹿。若果當前時區(qū)為東八區(qū)，則會對時間+8谎脯；
3. 結(jié)論
- 不考慮SQL中指定時區(qū)葱跋，則mysql在讀取或?qū)懭霑r不會對datetime根據(jù)時區(qū)來做轉(zhuǎn)換；
- JDBC會調(diào)解時區(qū)源梭，所以要保證mysql服務(wù)器和serverTimezone一致娱俺；
參考資料
1.Mysql 官網(wǎng)
11.2.2 The DATE, DATETIME, and TIMESTAMP Types
10.9 Date and Time Data Type Representation（存儲格式）
5.1.14 MySQL Server Time Zone Support
5.3 Configuration Properties for Connector/J
6.3 Configuration Properties
https://dev.mysql.com/doc/index-connectors.html
函數(shù)unix-timestamp
6.5 Java, JDBC, and MySQL Types
2.其他
https://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-a-database-connection-using-java
How to Set the JVM Time Zone
https://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issue
https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql/16066034
一次JDBC與MySQL因“CST”時區(qū)協(xié)商誤解導(dǎo)致時間差了14或13小時的排錯經(jīng)歷
時間戳（UnixTimestamp）與 《2038年問題》
Java與MySQL時間戳傳遞/存儲/協(xié)調(diào)問題--userLegacyDatetimeCode--userTimezone--serverTimezone
