MySQL 5.6 timestamp和datetime區別
MySQL會根據當前時區轉化TIMESTAMP值,在查詢時候會根據當前時區來處理。
mysql> create table test2(a int(20));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test2 values(1466929145);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+------------+
| a |
+------------+
| 1466929145 |
+------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 08:19:05 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into test2 values(14669291450);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 08:19:05 |
| 2038-01-19 03:14:07 |
+---------------------+
2 rows in set (0.00 sec)
mysql> insert into test2 values(14669291450);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 08:19:05 |
| 2038-01-19 03:14:07 |
| 2038-01-19 03:14:07 |
+---------------------+
3 rows in set (0.00 sec)
檢視當前時區
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +00:00 |
+---------------+--------+
1 row in set (0.00 sec)
更改會話時區引數後,可以看到查詢出來的時間發生了變化
mysql> set session time_zone='+01:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 09:19:05 |
| 2038-01-19 04:14:07 |
| 2038-01-19 04:14:07 |
+---------------------+
3 rows in set (0.00 sec)
datetime不受時區的影響
mysql> create table test3(a datetime);
Query OK, 0 rows affected (0.10 sec)
mysql> select * from test3;
Empty set (0.00 sec)
mysql> insert into test3 values(now());
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test3;
+---------------------+
| a |
+---------------------+
| 2017-02-13 10:02:20 |
+---------------------+
1 row in set (0.00 sec)
mysql> set session time_zone='+10:00';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +10:00 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> select * from test3;
+---------------------+
| a |
+---------------------+
| 2017-02-13 10:02:20 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table test2(a int(20));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test2 values(1466929145);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+------------+
| a |
+------------+
| 1466929145 |
+------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 08:19:05 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into test2 values(14669291450);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 08:19:05 |
| 2038-01-19 03:14:07 |
+---------------------+
2 rows in set (0.00 sec)
mysql> insert into test2 values(14669291450);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 08:19:05 |
| 2038-01-19 03:14:07 |
| 2038-01-19 03:14:07 |
+---------------------+
3 rows in set (0.00 sec)
檢視當前時區
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +00:00 |
+---------------+--------+
1 row in set (0.00 sec)
更改會話時區引數後,可以看到查詢出來的時間發生了變化
mysql> set session time_zone='+01:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select from_unixtime(a) from test2;
+---------------------+
| from_unixtime(a) |
+---------------------+
| 2016-06-26 09:19:05 |
| 2038-01-19 04:14:07 |
| 2038-01-19 04:14:07 |
+---------------------+
3 rows in set (0.00 sec)
datetime不受時區的影響
mysql> create table test3(a datetime);
Query OK, 0 rows affected (0.10 sec)
mysql> select * from test3;
Empty set (0.00 sec)
mysql> insert into test3 values(now());
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test3;
+---------------------+
| a |
+---------------------+
| 2017-02-13 10:02:20 |
+---------------------+
1 row in set (0.00 sec)
mysql> set session time_zone='+10:00';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +10:00 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> select * from test3;
+---------------------+
| a |
+---------------------+
| 2017-02-13 10:02:20 |
+---------------------+
1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2133471/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL中datetime和timestamp的區別MySql
- mysql資料庫date 、datetime、time、timestamp區別MySql資料庫
- MySQL 的 timestamp 和 datetime 型別比較MySql型別
- MySQL案例之Timestamp和DatetimeMySql
- MySQL: datetime vs timestampMySql
- QXMySQL 中 datetime 和 timestamp 的區別與選擇lypMySql
- Mysql中的Datetime和Timestamp比較MySql
- Mysql日期(DATE, TIME, DATETIME, TIMESTAMP)型別的比較MySql型別
- python date 和 datetime 的取值範圍(對比 Mysql 的 datetime 和 timestamp)PythonMySql
- java:Date和DateTime區別Java
- 面試題:能談談Date、Datetime、Time、Timestamp、year的區別嗎?面試題
- MySQL5日期型別DATETIME和TIMESTAMP相關問題詳解薦MySql型別
- Sybase datetime型別對映為Oracle timestamp型別Oracle
- Oracle中Date和Timestamp的區別Oracle
- datetime、datetime2的區別
- Mysql - 如何決定用 datetime、timestamp、int 哪種型別儲存時間戳?MySql型別時間戳
- MySQL 5.7 datetime和timestamp欄位設定default 0 插入資料包錯MySql
- mysql bigint型別和datetime型別的轉換MySql型別
- Mysql時間欄位格式如何選擇,TIMESTAMP,DATETIME,INT?MySql
- mysql資料庫時間型別datetime、bigint、timestamp的查詢效率比較MySql資料庫型別
- MySQL資料庫中的timestamp型別與時區MySql資料庫型別
- TIMESTAMP型別的時區型別
- mysql 資料型別TIMESTAMPMySQL 資料型別
- Python中time和datetime的區別與聯絡Python
- mysql CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMPMySql
- mysql中的date、datetime、timestamp你還不知道怎麼使用嗎MySql
- timestamp with local time zone型別和timestamp with time zone型別
- MYSQL和SQL的區別MySql
- mysql中!=和is not的區別MySql
- mysql中“ ‘ “和 “ ` “的區別MySql
- MySQL和Oracle的區別MySqlOracle
- Oracle和MySQL的區別OracleMySql
- MySQL中CHAR和VARCHAR區別MySql
- 【轉】mysql 和 redis的區別MySqlRedis
- MS SQL Server和MySQL區別ServerMySql
- MySQL之timestampMySql
- SQLserver-MySQL的區別和用法ServerMySql
- mongodb和mysql有什麼區別MongoDBMySql