MySQL 5.6 timestamp和datetime区别

左手的ㄟ右手 2024-04-18 00:31 68阅读 0赞

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2133471/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2133471/