MySQL数据类型--------日期与时间类型实战

hitl

　　1. 背景
　　* MySQL支持多种表示日期和时间的数据类型，如YEAR、TIME、DATETIME、TIMESTAMP等等……
　　* MySQL 5.6.4版本开始支持微秒(支持类型：TIMTE、DATETIME、TIMESTAMP,　微秒长度最大为6)
　　2. 日期与时间类型所属空间与表示范围
类型　　5.6.4版本之前
　　占用字节
　　5.6.4版本之后
　　(包含5.6.3版本)
　　占用字节
表示范围DATETIME86+(微秒长度-1 )/21000-01-01 00:00:00 ~ 9999-12-31 23:59:59DATE331000-01-01 ~ 9999-12-31TIMESTAMP44+(微秒长度-1 )/21970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTCYEAR11　　YEAR(2): 1970 ~ 2070
　　YEAR(4): 1901 ~ 2155
TIME33+(微秒长度-1 )/2-838:59:59 ~ 838:59:59　　3. 日期与时间类型 "0" 值格式
类型Zero ValueDATETIME'0000-00-00 00:00:00'DATE'0000-00-00'TIMESTAMP'0000-00-00 00:00:00'YEAR0000TIME'00:00:00'　　4. 日期与时间类型相关函数操作
　　* NOW() 获取语句开始执行的日期和时间，[ MySQL 5.6.3版本之后可以获取微秒 ]
mysql> SELECT NOW();　　
+---------------------+
　　
| NOW()               |
　　
+---------------------+
　　
| 2017-06-27 09:00:13 |
　　
+---------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> SELECT NOW(6);
　　
+----------------------------+
　　
| NOW(6)                     |
　　
+----------------------------+
　　
| 2017-06-27 09:18:50.720849 |
　　
+----------------------------+
　　
1 row in set (0.00 sec)
　　* SYSDATE() 获取SYSDATE()函数执行的时间
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();　　
+---------------------+----------+---------------------+
　　
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
　　
+---------------------+----------+---------------------+
　　
| 2017-06-27 09:38:03 |        0 | 2017-06-27 09:38:05 |
　　
+---------------------+----------+---------------------+
　　
1 row in set (2.01 sec)
　　* CURDATE() 获取当前日期
mysql> SELECT CURDATE();　　
+------------+
　　
| CURDATE()  |
　　
+------------+
　　
| 2017-06-27 |
　　
+------------+
　　
1 row in set (0.00 sec)
　　* CURTIME() 获取当前时间
mysql> SELECT CURTIME();　　
+-----------+
　　
| CURTIME() |
　　
+-----------+
　　
| 09:05:51  |
　　
+-----------+
　　
1 row in set (0.03 sec)
　　* ADDDATE() 增加日期
　　[ 在当前日期上增加 10 天 ]
mysql> SELECT CURDATE(), ADDDATE(CURDATE(), "10");　　
+------------+--------------------------+
　　
| CURDATE()  | ADDDATE(CURDATE(), "10") |
　　
+------------+--------------------------+
　　
| 2017-06-27 | 2017-07-07               |
　　
+------------+--------------------------+
　　
1 row in set (0.02 sec)
　　* ADDTIME() 增加时间
　　[ 在当前时间上增加1天1小时1分1秒 ]
mysql> SELECT NOW(), ADDTIME(NOW(), '1 1:1:1');　　
+---------------------+---------------------------+
　　
| NOW()               | ADDTIME(NOW(), '1 1:1:1') |
　　
+---------------------+---------------------------+
　　
| 2017-06-27 09:00:21 | 2017-06-28 10:01:22       |
　　
+---------------------+---------------------------+
　　
1 row in set (0.02 sec)
　　* DATE() 返回字符串中的日期
mysql> SELECT NOW(), DATE(NOW());　　
+---------------------+-------------+
　　
| NOW()               | DATE(NOW()) |
　　
+---------------------+-------------+
　　
| 2017-06-27 09:07:18 | 2017-06-27  |
　　
+---------------------+-------------+
　　
1 row in set (0.00 sec)
　　* TIME() 返回字符串中的时间
mysql> SELECT NOW(), TIME(NOW());　　
+---------------------+-------------+
　　
| NOW()               | TIME(NOW()) |
　　
+---------------------+-------------+
　　
| 2017-06-27 09:07:40 | 09:07:40    |
　　
+---------------------+-------------+
　　
1 row in set (0.01 sec)
　　* YEAR() 返回字符串中的年数
mysql> SELECT NOW(), YEAR(NOW());　　
+---------------------+-------------+
　　
| NOW()               | YEAR(NOW()) |
　　
+---------------------+-------------+
　　
| 2017-06-27 09:12:39 |        2017 |
　　
+---------------------+-------------+
　　
1 row in set (0.00 sec)
　　* MONTH() 返回字符串中的月数
mysql> SELECT NOW(), MONTH(NOW());　　
+---------------------+--------------+
　　
| NOW()               | MONTH(NOW()) |
　　
+---------------------+--------------+
　　
| 2017-06-27 09:13:09 |            6 |
　　
+---------------------+--------------+
　　
1 row in set (0.01 sec)
　　* DAY() 返回字符串中的日数
mysql> SELECT NOW(), DAY(NOW());　　
+---------------------+------------+
　　
| NOW()               | DAY(NOW()) |
　　
+---------------------+------------+
　　
| 2017-06-27 09:12:08 |         27 |
　　
+---------------------+------------+
　　
1 row in set (0.01 sec)
　　* HOUR() 返回字符串中的小时数
mysql> SELECT NOW(), HOUR(NOW());　　
+---------------------+-------------+
　　
| NOW()               | HOUR(NOW()) |
　　
+---------------------+-------------+
　　
| 2017-06-27 09:13:57 |           9 |
　　
+---------------------+-------------+
　　
1 row in set (0.00 sec)
　　* MINUTE() 返回字符串中的分钟数
mysql> SELECT NOW(), MINUTE(NOW());　　
+---------------------+---------------+
　　
| NOW()               | MINUTE(NOW()) |
　　
+---------------------+---------------+
　　
| 2017-06-27 09:15:38 |            15 |
　　
+---------------------+---------------+
　　
1 row in set (0.00 sec)
　　* SECOND() 返回字符串中的秒数
mysql> SELECT NOW(), SECOND(NOW());　　
+---------------------+---------------+
　　
| NOW()               | SECOND(NOW()) |
　　
+---------------------+---------------+
　　
| 2017-06-27 09:16:23 |            23 |
　　
+---------------------+---------------+
　　
1 row in set (0.02 sec)
　　* MICROSECOND()　返回字符串中的微秒数　[MySQL 5.6.3版本之后]
mysql> SELECT NOW(6), MICROSECOND(NOW(6));　　
+----------------------------+---------------------+
　　
| NOW(6)                     | MICROSECOND(NOW(6)) |
　　
+----------------------------+---------------------+
　　
| 2017-06-27 09:18:11.264125 |              264125 |
　　
+----------------------------+---------------------+
　　
1 row in set (0.01 sec)
　　* DATEDIFF() 日期相减运算
mysql> SELECT DATEDIFF('2017-06-29', '2017-06-09');　　
+--------------------------------------+
　　
| DATEDIFF('2017-06-29', '2017-06-09') |
　　
+--------------------------------------+
　　
|                                   20 |
　　
+--------------------------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> SELECT DATEDIFF('2017-06-29', '2017-07-09');
　　
+--------------------------------------+
　　
| DATEDIFF('2017-06-29', '2017-07-09') |
　　
+--------------------------------------+
　　
|                                  -10 |
　　
+--------------------------------------+
　　
1 row in set (0.03 sec)
　　* DAYNAME() 返回工作日的英文名称
mysql> SELECT CURDATE(), DAYNAME(CURDATE());　　
+------------+--------------------+
　　
| CURDATE()  | DAYNAME(CURDATE()) |
　　
+------------+--------------------+
　　
| 2017-06-27 | Tuesday            |
　　
+------------+--------------------+
　　
1 row in set (0.01 sec)
　　* MONTHNAME() 返回月的英文名称
mysql> SELECT CURDATE(), MONTHNAME(CURDATE());　　
+------------+----------------------+
　　
| CURDATE()  | MONTHNAME(CURDATE()) |
　　
+------------+----------------------+
　　
| 2017-06-27 | June                 |
　　
+------------+----------------------+
　　
1 row in set (0.01 sec)
　　* PERIOD_DIFF() 月数相减运算 [ 格式是YYYYMM或者YYMM ]
mysql> SELECT PERIOD_DIFF(201706, 201705);　　
+-----------------------------+
　　
| PERIOD_DIFF(201706, 201705) |
　　
+-----------------------------+
　　
|                           1 |
　　
+-----------------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> SELECT PERIOD_DIFF(201706, 201707);
　　
+-----------------------------+
　　
| PERIOD_DIFF(201706, 201707) |
　　
+-----------------------------+
　　
|                          -1 |
　　
+-----------------------------+
　　
1 row in set (0.00 sec)
　　…………等等，详情查看官网手册。
　　5. TIMESTAMP 和 DATETIME 区别 [ TIMESTAMP VS DATETIME]
　　* timestamp相比较datetime所占空间字节小。
　　* timestamp容易所支持的范围比timedate要小。 并且容易出现超出的情况
　　* timestamp受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响, 而datetime不受时区影响.
　　6. 时间影响实验
　　* 创建表 t_d
mysql> CREATE TABLE t_d(　　
    -> t TIMESTAMP,
　　
    -> d DATETIME
　　
    -> )ENGINE=INNODB CHARSET=utf8mb4;
　　
Query OK, 0 rows affected (0.09 sec)
　　* 查看时区
mysql> show variables like '%zone%';　　
+------------------+--------+
　　
| Variable_name    | Value  |
　　
+------------------+--------+
　　
| system_time_zone | CST    |
　　
| time_zone        | SYSTEM |
　　
+------------------+--------+
　　
2 rows in set (0.02 sec)
　　* 插入数据
mysql> INSERT INTO t_d SELECT NOW(), NOW();　　
Query OK, 1 row affected (0.01 sec)
　　
Records: 1  Duplicates: 0  Warnings: 0
　　* 查看数据
mysql> SELECT * FROM t_d;　　
+---------------------+---------------------+
　　
| t                   | d                   |
　　
+---------------------+---------------------+
　　
| 2017-06-27 09:44:13 | 2017-06-27 09:44:13 |
　　
+---------------------+---------------------+
　　
1 row in set (0.00 sec)
　　* 更新时区
mysql> set time_zone = '+0:00';　　
Query OK, 0 rows affected (0.00 sec)
　　* 再次查看数据
mysql> SELECT * FROM t_d;　　
+---------------------+---------------------+
　　
| t                   | d                   |
　　
+---------------------+---------------------+
　　
| 2017-06-27 01:44:13 | 2017-06-27 09:44:13 |
　　
+---------------------+---------------------+
　　
1 row in set (0.01 sec)
　　7. 总结
　　以需求驱动技术，技术本身没有优略之分，只有业务之分。