mysql 时间处理_MySQL时间日期处理函数

最新推荐文章于 2024-04-08 06:31:35 发布

釹王控小桃花

最新推荐文章于 2024-04-08 06:31:35 发布

阅读量91

点赞数

文章标签： mysql 时间处理

本文链接：https://blog.csdn.net/weixin_29938119/article/details/113338647

版权

NOW()

返回当前的日期和时间

mysql> SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2019-03-25 23:09:39 |

+---------------------+

1 row in set (0.04 sec)

DATE(), YEAR(), MONTH(), DAY(), TIME() ...

使用这些时间、日期的关键词，会返回一个日期的指定部分

mysql> SELECT DATE(NOW());

+-------------+

| DATE(NOW()) |

+-------------+

| 2019-03-26 |

+-------------+

1 row in set (0.03 sec)

mysql> SELECT DAY(NOW());

+------------+

| DAY(NOW()) |

+------------+

| 26 |

+------------+

1 row in set (0.04 sec)

mysql> SELECT YEAR(NOW());

+-------------+

| YEAR(NOW()) |

+-------------+

| 2019 |

+-------------+

1 row in set (0.03 sec)

mysql> SELECT MONTH(NOW());

+--------------+

| MONTH(NOW()) |

+--------------+

| 3 |

+--------------+

1 row in set (0.00 sec)

mysql> SELECT TIME(NOW());

+-------------+

| TIME(NOW()) |

+-------------+

| 21:04:03 |

+-------------+

1 row in set (0.04 sec)

mysql> SELECT SECOND(NOW());

+---------------+

| SECOND(NOW()) |

+---------------+

| 12 |

+---------------+

1 row in set (0.00 sec)

CURDATE()

返回当前的日期

mysql> SELECT CURDATE();

+------------+

| CURDATE() |

+------------+

| 2019-03-25 |

+------------+

1 row in set (0.03 sec)

CURTIME()

返回当前的时间

mysql> SELECT CURTIME();

+-----------+

| CURTIME() |

+-----------+

| 23:10:47 |

+-----------+

1 row in set (0.00 sec)

EXTRACT()

按要求返回日期/时间单独部分

mysql> SELECT EXTRACT(YEAR FROM NOW());

+--------------------------+

| EXTRACT(YEAR FROM NOW()) |

+--------------------------+

| 2019 |

+--------------------------+

1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM NOW());

+--------------------------------+

| EXTRACT(YEAR_MONTH FROM NOW()) |

+--------------------------------+

| 201903 |

+--------------------------------+

1 row in set (0.00 sec)

mysql> SELECT EXTRACT(DAY FROM NOW());

+-------------------------+

| EXTRACT(DAY FROM NOW()) |

+-------------------------+

| 25 |

+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT EXTRACT(MINUTE FROM NOW());

+----------------------------+

| EXTRACT(MINUTE FROM NOW()) |

+----------------------------+

| 22 |

+----------------------------+

1 row in set (0.00 sec)

mysql> SELECT EXTRACT(HOUR_MINUTE FROM NOW());

+---------------------------------+

| EXTRACT(HOUR_MINUTE FROM NOW()) |

+---------------------------------+

| 2322 |

+---------------------------------+

DATE_ADD , DATE_SUB

DATE_ADD给日期添加指定的时间间隔;

DATE_SUB从日期减去指定的时间间隔;

mysql> SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2019-03-25 23:37:17 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 28 DAY);

+----------------------------------+

| DATE_ADD(NOW(), INTERVAL 28 DAY) |

+----------------------------------+

| 2019-04-22 23:37:20 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);

+----------------------------------+

| DATE_ADD(NOW(), INTERVAL 1 HOUR) |

+----------------------------------+

| 2019-03-26 00:37:22 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL '12:18' MINUTE_SECOND);

+-------------------------------------------------+

| DATE_ADD(NOW(), INTERVAL '12:18' MINUTE_SECOND) |

+-------------------------------------------------+

| 2019-03-25 23:49:59 |

+-------------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(NOW(), INTERVAL 10 YEAR);

+-----------------------------------+

| DATE_SUB(NOW(), INTERVAL 10 YEAR) |

+-----------------------------------+

| 2009-03-25 23:39:29 |

+-----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(NOW(), INTERVAL '8-2' YEAR_MONTH);

+--------------------------------------------+

| DATE_SUB(NOW(), INTERVAL '8-2' YEAR_MONTH) |

+--------------------------------------------+

| 2011-01-25 23:40:02 |

+--------------------------------------------+

1 row in set (0.00 sec)

ADDDATE(), ADDTIME(), SUBDATE(), SUBTIME()

给日期/时间，加上/减去指定的日期/时间间隔。因为函数已声明加减的间隔为时间或日期，因此不必像DATE_ADD和DATE_SUB方法那样再在参数列表中声名INTERVAL 1 DAY一类。

mysql> SELECT ADDDATE(NOW(),31);

+---------------------+

| ADDDATE(NOW(),31) |

+---------------------+

| 2019-04-26 21:39:02 |

+---------------------+

1 row in set (0.03 sec)

mysql> SELECT SUBDATE(NOW(),31);

+---------------------+

| SUBDATE(NOW(),31) |

+---------------------+

| 2019-02-23 21:39:08 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(),'1:0:0');

+------------------------+

| ADDTIME(NOW(),'1:0:0') |

+------------------------+

| 2019-03-26 22:41:10 |

+------------------------+

1 row in set (0.04 sec)

mysql> SELECT SUBTIME(NOW(),'1:0:0');

+------------------------+

| SUBTIME(NOW(),'1:0:0') |

+------------------------+

| 2019-03-26 20:41:20 |

+------------------------+

1 row in set (0.00 sec)

DATEDIFF

返回两个日期之间的天数

mysql> SELECT DATEDIFF(NOW(),'2008-8-8');

+----------------------------+

| DATEDIFF(NOW(),'2008-8-8') |

+----------------------------+

| 3881 |

+----------------------------+

1 row in set (0.05 sec)

mysql> SELECT DATEDIFF('2008-8-8',NOW());

+----------------------------+

| DATEDIFF('2008-8-8',NOW()) |

+----------------------------+

| -3881 |

+----------------------------+

1 row in set (0.00 sec)

DATE_FORMAT

用不同的格式显示日期/时间

下表为一些格式说明符：

说明符

描述

缩写星期名(Sun..Sat)

缩写月名(Jan..Dec)

数字月份(0..12)

带有英文后缀的月中的天(0th, 1st, 2nd,...)

月中的天，数值(00-31)

月中的天，数值(0-31)

微秒(000000..999999)

小时 (00..23)

小时 (01..12)

分钟，数值(00..59)

年中的天 (001..366)

小时 (0..23)

小时 (1..12)

月名(January..December)

月，数值(00..12)

AM 或 PM

时间，12小时(hh:mm:ss AM 或 PM)

秒(00..59)

时间, 24小时制 (hh:mm:ss)

周 (00..53) , 星期日是一周的第一天

周 (00..53), 星期一是一周的第一天

周 (01..53) 星期日是一周的第一天，与 %X 使用

周 (01..53) 星期一是一周的第一天，与 %x 使用

星期名 (Sunday..Saturday)

周中的天 (0=星期日, 6=星期六)

年，其中的星期日是周的第一天，4 位，与 %V 使用

年，其中的星期一是周的第一天，4 位，与 %v 使用

年，4 位

年，2 位

mysql> SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2019-03-26 21:30:50 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%W %M %Y');

+-------------------------------+

| DATE_FORMAT(NOW(),'%W %M %Y') |

+-------------------------------+

| Tuesday March 2019 |

+-------------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%H:%i:%s');

+-------------------------------+

| DATE_FORMAT(NOW(),'%H:%i:%s') |

+-------------------------------+

| 21:31:23 |

+-------------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');

+----------------------------------------+

| DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') |

+----------------------------------------+

| Mar 26 2019 09:32 PM |

+----------------------------------------+

1 row in set (0.00 sec)

釹王控小桃花

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 时间处理_MySQL时间日期处理函数

NOW()返回当前的日期和时间mysql> SELECT NOW();+---------------------+| NOW() |+---------------------+| 2019-03-25 23:09:39 |+---------------------+1 row in set (0.04 sec)DATE(), YEAR(), MONTH(),...
复制链接

扫一扫