1. 背景
* MySQL支持多种表示日期和时间的数据类型，如YEAR、TIME、DATETIME、TIMESTAMP等等……
* MySQL 5.6.4版本开始支持微秒(支持类型：TIMTE、DATETIME、TIMESTAMP, 微秒长度最大为6)
2. 日期与时间类型所属空间与表示范围
| 类型 | 5.6.4版本之前 占用字节 |
5.6.4版本之后 (包含5.6.3版本) 占用字节 |
表示范围 |
| DATETIME | 8 | 6+(微秒长度-1 )/2 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| DATE | 3 | 3 |
1000-01-01 ~ 9999-12-31 |
| TIMESTAMP | 4 | 4+(微秒长度-1 )/2 | 1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC |
| YEAR | 1 | 1 | YEAR(2): 1970 ~ 2070 YEAR(4): 1901 ~ 2155 |
| TIME |
3 | 3+(微秒长度-1 )/2 | -838:59:59 ~ 838:59:59 |
3. 日期与时间类型 "0" 值格式
| 类型 | Zero Value |
| DATETIME | '0000-00-00 00:00:00' |
| DATE | '0000-00-00' |
| TIMESTAMP | '0000-00-00 00:00:00' |
| YEAR | 0000 |
| TIME | '00:00:00' |
4. 日期与时间类型相关函数操作
* NOW() 获取语句开始执行的日期和时间，[ MySQL 5.6.3版本之后可以获取微秒 ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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()函数执行的时间
|
1
2
3
4
5
6
7
|
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() 获取当前日期
|
1
2
3
4
5
6
7
|
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2017-06-27 |
+------------+
1 row
in
set
(0.00 sec)
|
* CURTIME() 获取当前时间
|
1
2
3
4
5
6
7
|
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 09:05:51 |
+-----------+
1 row
in
set
(0.03 sec)
|
* ADDDATE() 增加日期
[ 在当前日期上增加 10 天 ]
|
1
2
3
4
5
6
7
|
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秒 ]
|
1
2
3
4
5
6
7
|
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() 返回字符串中的日期
|
1
2
3
4
5
6
7
|
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() 返回字符串中的时间
|
1
2
3
4
5
6
7
|
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() 返回字符串中的年数
|
1
2
3
4
5
6
7
|
mysql> SELECT NOW(), YEAR(NOW());
+---------------------+-------------+
| NOW() | YEAR(NOW()) |
+---------------------+-------------+
| 2017-06-27 09:12:39 | 2017 |
+---------------------+-------------+
1 row
in
set
(0.00 sec)
|
* MONTH() 返回字符串中的月数
|
1
2
3
4
5
6
7
|
mysql> SELECT NOW(), MONTH(NOW());
+---------------------+--------------+
| NOW() | MONTH(NOW()) |
+---------------------+--------------+
| 2017-06-27 09:13:09 | 6 |
+---------------------+--------------+
1 row
in
set
(0.01 sec)
|
* DAY() 返回字符串中的日数
|
1
2
3
4
5
6
7
|
mysql> SELECT NOW(), DAY(NOW());
+---------------------+------------+
| NOW() | DAY(NOW()) |
+---------------------+------------+
| 2017-06-27 09:12:08 | 27 |
+---------------------+------------+
1 row
in
set
(0.01 sec)
|
* HOUR() 返回字符串中的小时数
|
1
2
3
4
5
6
7
|
mysql> SELECT NOW(), HOUR(NOW());
+---------------------+-------------+
| NOW() | HOUR(NOW()) |
+---------------------+-------------+
| 2017-06-27 09:13:57 | 9 |
+---------------------+-------------+
1 row
in
set
(0.00 sec)
|
* MINUTE() 返回字符串中的分钟数
|
1
2
3
4
5
6
7
|
mysql> SELECT NOW(), MINUTE(NOW());
+---------------------+---------------+
| NOW() | MINUTE(NOW()) |
+---------------------+---------------+
| 2017-06-27 09:15:38 | 15 |
+---------------------+---------------+
1 row
in
set
(0.00 sec)
|
* SECOND() 返回字符串中的秒数
|
1
2
3
4
5
6
7
|
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版本之后]
|
1
2
3
4
5
6
7
|
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() 日期相减运算
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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() 返回工作日的英文名称
|
1
2
3
4
5
6
7
|
mysql> SELECT CURDATE(), DAYNAME(CURDATE());
+------------+--------------------+
| CURDATE() | DAYNAME(CURDATE()) |
+------------+--------------------+
| 2017-06-27 | Tuesday |
+------------+--------------------+
1 row
in
set
(0.01 sec)
|
* MONTHNAME() 返回月的英文名称
|
1
2
3
4
5
6
7
|
mysql> SELECT CURDATE(), MONTHNAME(CURDATE());
+------------+----------------------+
| CURDATE() | MONTHNAME(CURDATE()) |
+------------+----------------------+
| 2017-06-27 | June |
+------------+----------------------+
1 row
in
set
(0.01 sec)
|
* PERIOD_DIFF() 月数相减运算 [ 格式是YYYYMM或者YYMM ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
|
1
2
3
4
5
|
mysql> CREATE TABLE t_d(
-> t TIMESTAMP,
-> d DATETIME
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.09 sec)
|
* 查看时区
|
1
2
3
4
5
6
7
8
|
mysql> show variables like
'%zone%'
;
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows
in
set
(0.02 sec)
|
* 插入数据
|
1
2
3
|
mysql> INSERT INTO t_d SELECT NOW(), NOW();
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
|
* 查看数据
|
1
2
3
4
5
6
7
|
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)
|
* 更新时区
|
1
2
|
mysql>
set
time_zone =
'+0:00'
;
Query OK, 0 rows affected (0.00 sec)
|
* 再次查看数据
|
1
2
3
4
5
6
7
|
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. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943290，如需转载请自行联系原作者
