MySQL数据类型 -- 日期时间型

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.7.22-log |
+---------------+------------+

mysql> CREATE TABLE tb_date_time
    -> (
    -> col1 date,
    -> col2 time,
    -> col3 datetime,
    -> col4 timestamp DEFAULT current_timestamp
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> select 'Leshami' as Author
    -> union all
    -> select 'http://blog.csdn.net/leshami';
+------------------------------+
| Author |
+------------------------------+
| Leshami |
| http://blog.csdn.net/leshami |
+------------------------------+

mysql> INSERT INTO tb_date_time(col1, col2, col3)
    -> VALUES (now(), now(), now());
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SELECT * FROM tb_date_time;
+------------+----------+---------------------+---------------------+
| col1 | col2 | col3 | col4 |
+------------+----------+---------------------+---------------------+
| 2018-09-23 | 18:07:13 | 2018-09-23 18:07:13 | 2018-09-23 18:07:13 |
+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

-- 查看系统日期时间
mysql> system date
Sun Sep 23 18:08:18 CST 2018

-- 在mysql层临时修改一下时区，假定在这里修改为+7时区
mysql> set time_zone='+7:00';
Query OK, 0 rows affected (0.00 sec)

-- 再次查看表，col4列上的时间则提前了一小时
-- 也即是时区会影响时间戳列

mysql> SELECT * FROM tb_date_time;
+------------+----------+---------------------+---------------------+
| col1 | col2 | col3 | col4 |
+------------+----------+---------------------+---------------------+
| 2018-09-23 | 18:07:13 | 2018-09-23 18:07:13 | 2018-09-23 17:07:13 |
+------------+----------+---------------------+---------------------+

注意对比TIMESTAMP与DATETIME
TIMESTAMP需要4个字节，而DATETIME需要5个字节。 
TIMESTAMP和DATETIME都需要额外的字节来获得小数秒的精度。
TIMESTAMP值的范围为1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC。 
如果要存储超过2038的时间值，则应使用DATETIME而不是TIMESTAMP。

-- 查看当前时间
mysql> SET @dt = NOW();
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @dt;
+---------------------+
| @dt |
+---------------------+
| 2018-09-23 18:08:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(@dt);
+------------+
| DATE(@dt) |
+------------+
| 2018-09-23 |
+------------+
1 row in set (0.00 sec)