mysql时间的数据类型_MySQL数据类型--------日期与时间类型实战

最新推荐文章于 2022-08-31 21:44:25 发布

吃土豆不吐土豆泥

最新推荐文章于 2022-08-31 21:44:25 发布

阅读量197

点赞数

文章标签： mysql时间的数据类型

本文链接：https://blog.csdn.net/weixin_31226805/article/details/114876280

版权

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

6+(微秒长度-1 )/2

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

DATE

1000-01-01 ~ 9999-12-31

TIMESTAMP

4+(微秒长度-1 )/2

1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC

YEAR

YEAR(2): 1970 ~ 2070

YEAR(4): 1901 ~ 2155

TIME

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

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

吃土豆不吐土豆泥

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql时间的数据类型_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版本)占用字节表示范围DATETIME86+(微秒...
复制链接

扫一扫