mysql 5.6 timestamp_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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值