MySQL timestamp的類型與時區
MySQL的timestamp類型時間范圍between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'，超出這個范圍則值記錄為'0000-00-00 00:00:00'，該類型的一個重要特點就是保存的時間與時區密切相關，上述所說的時間范圍是UTC(Universal Time Coordinated)標準，指的是經度0度上的標準時間，我國日常生活中時區以首都北京所處的東半球第8區為基準，統一使用東8區時間(俗稱北京時間)，比UTC要早8個小時，服務器的時區設置也遵照此標準，因此對應過來timestamp的時間范圍則應校準為'1970-01-01 08:00:01' and '2038-01-19 11:14:07'，也就是說東八區的1970-1-1 08:00:01等同于UTC 1970-1-1 00:00:01。
需要特點注意，timestamp類型的時間不僅僅與寫入記錄時的時區有關，顯示時也與時區有關，例如：
| mysql> desc j1_dt;+-------+-----------+------+-----+-------------------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------+------+-----+-------------------+-------+| dt | timestamp | NO | | CURRENT_TIMESTAMP | | +-------+-----------+------+-----+-------------------+-------+1 row in set (0.00 sec)mysql> insert into j1_dt values ('1970-01-01 08:00:01');Query OK, 1 row affected (0.00 sec)mysql> select * from j1_dt;+---------------------+| dt |+---------------------+| 1970-01-01 08:00:01 | +---------------------+1 row in set (0.00 sec)mysql> set time_zone='+0:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from j1_dt;+---------------------+| dt |+---------------------+| 1970-01-01 00:00:01 | +---------------------+1 row in set (0.00 sec)mysql> set time_zone='+1:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from j1_dt;+---------------------+| dt |+---------------------+| 1970-01-01 01:00:01 | +---------------------+1 row in set (0.00 sec) |
如上述所示，根據時區的不同，顯示的日期也是不一樣的，這正是timestamp類型在MySQL日期類型中獨有的時區特點。
如果向timestamp類型列插入的值超出了指定范圍，則實際實際保存的值為'0000-00-00 00:00:00'，并觸發一個警告信息：
| mysql> set time_zone='+8:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from j1_dt;+---------------------+| dt |+---------------------+| 1970-01-01 08:00:01 | +---------------------+1 row in set (0.00 sec)mysql> insert into j1_dt values ('1970-01-01 00:00:01');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+------------------------------------------------------+| Level | Code | Message |+---------+------+------------------------------------------------------+| Warning | 1264 | Out of range value adjusted for column 'dt' at row 1 | +---------+------+------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from j1_dt;+---------------------+| dt |+---------------------+| 1970-01-01 08:00:01 | | 0000-00-00 00:00:00 | +---------------------+2 rows in set (0.00 sec) |
