mysql datetime详解_MySQL中datetime和timestamp的区别及使用详解

一、MySQL中如何表示当前时间？

其实，表达方式还是蛮多的，汇总如下：

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

NOW()

LOCALTIME

LOCALTIME()

LOCALTIMESTAMP

LOCALTIMESTAMP()

二、关于TIMESTAMP和DATETIME的比较

一个完整的日期格式如下：YYYY-MM-DD HH:MM:SS[.fraction]，它可分为两部分：date部分和time部分，其中，date部分对应格式中的“YYYY-MM-DD”，time部分对应格式中的“HH:MM:SS[.fraction]”。对于date字段来说，它只支持date部分，如果插入了time部分的内容，它会丢弃掉该部分的内容，并提示一个warning。

如下所示：

mysql> create table test(id int,hiredate date);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'20151208104400');

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warning;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1

mysql> select * from test;

+------+------------+

| id | hiredate |

+------+------------+

| 1 | 2015-12-08 |

| 1 | 2015-12-08 |

+------+------------+

2 rows in set (0.00 sec)

注：第一个没提示warning的原因在于它的time部分都是0

TIMESTAMP和DATETIME的相同点：

1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

TIMESTAMP和DATETIME的不同点：

1> 两者的存储方式不一样

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时，将其又转化为客户端当前时区进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

下面，我们来验证一下

首先创建两种测试表，一个使用timestamp格式，一个使用datetime格式。

mysql> create table test(id int,hiredate timestamp);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');

Query OK, 1 row affected (0.00 sec)

mysql> create table test1(id int,hiredate datetime);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,'20151208000000');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+------+---------------------+

| id | hiredate |

+------+---------------------+

| 1 | 2015-12-08 00:00:00 |

+------+---------------------+

1 row in set (0.01 sec)

mysql> select * from test1;

+------+---------------------+

| id | hiredate |

+------+---------------------+

| 1 | 2015-12-08 00:00:00 |

+------+---------------------+

1 row in set (0.00 sec)

两者输出是一样的。

其次修改当前会话的时区

mysql> show variables like '%time_zone%';

+------------------+--------+

| Variable_name | Value |

+------------------+--------+

| system_time_zone | CST |

| time_zone | SYSTEM |

+------------------+--------+

2 rows in set (0.00 sec)

mysql> set time_zone='+0:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

+------+---------------------+

| id | hiredate |

+------+---------------------+

| 1 | 2015-12-07 16:00:00 |

+------+---------------------+

1 row in set (0.00 sec)

mysql> select * from test1;

+------+---------------------+

| id | hiredate |

+------+---------------------+

| 1 | 2015-12-08 00:00:00 |

+------+---------------------+

1 row in set (0.01 sec)