导航：首页 > 数据库 >

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

发表于：2024-11-13 作者：千家信息网编辑

千家信息网最后更新 2024年11月13日，1. 背景 * MySQL支持多种表示日期和时间的数据类型，如YEAR、TIME、DATETIME、TIMESTAMP等等……* MySQL 5.6.4版本开始支持微秒(支持类型：TIMTE、DATE

千家信息网最后更新 2024年11月13日MySQL数据类型--------日期与时间类型实战

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版本之后可以获取微秒 ]

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. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。

很赞哦！