mysql 根据出生日期计算年龄、日期格式化等常见日期处理相关函数使用汇总
本文分两部分，第一部分为Mysql 参考手册列举的日期、时间函数说明，第二部分为根据项目实践谈谈常用的Mysql日期、时间函数使用案例。
一、参考手册日期、时间函数说明
Mysql5.7参考手册13.7 Date and Time Functions章节列举了日期、时间相关的处理函数，根据用途，我们可以把mysql日期、时间函数分为4类。
1、“常量”值输出
如常见的now(), current_date()等，函数不区分大小写，具体如下表所示。
| 函数 | 功能 | DEMO |
|---|---|---|
| NOW() CURRENT_TIMESTAMP() | 返回当前日期、时间 | mysql>SELECT NOW(); ->‘2019-01-20 20:50:26’ |
| CURRENT_DATE() CURDATE() | 返回当前日期 | mysql>SELECT CURRENT_DATE(); ->‘2019-01-20’ |
| CURRENT_TIME() CURTIME() | 返回当前时间 | mysql>SELECT CURRENT_TIME(); ->‘20:50:26’ |
2、日期时间的构造和截取
| 函数 | 功能 | DEMO |
|---|---|---|
| MAKEDATE() | 根据年份、该年份的第几天返回日期 | SELECT MAKEDATE(2019,31), MAKEDATE(2019,32); ->‘2019-01-31’, ‘2019-02-01’ |
| MAKETIME() | 根据时、分、秒返回时间 | SELECT MAKETIME(12,15,30); ->‘12:15:30’ |
| DATE() | 获取日期部分 | SELECT DATE(‘2003-12-31 01:02:03’), date(now()) ->‘2003-12-31’, ‘2019-01-21’ |
| YEAR() | 获取日期的年份 | SELECT YEAR(‘1987-01-01’), year(now()) 1987, 2019 |
| QUARTER() | 获取季度 | SELECT QUARTER(‘2008-04-01’); -> 2 |
| MONTH() | 获取日期的月份 | SELECT MONTH(‘2008-02-03’), MONTH(NOW()) 2, 1 |
| DAY() DAYOFMONTH() | 获取月份的第几天 | SELECT DAYOFMONTH(‘2007-02-03’); -> 3 |
| HOUR() | 获取小时部分 | SELECT hour(‘07:02:03’),hour(now()) -> 7, 11 |
| MINUTE() | 获取分钟部分 | SELECT MINUTE(‘2008-02-03 10:05:03’); -> 5 |
| SECOND() | 获取秒 | SELECT SECOND(‘10:05:03’); -> 3 |
| MICROSECOND() | 获取微妙 | SELECT MICROSECOND(‘12:00:00.123’); -> 123 |
| EXTRACT() | 提取日期指定部分 | EXTRACT(YEAR FROM ‘2009-07-02’); -> 2009 EXTRACT(YEAR_MONTH FROM ‘2009-07-02 01:02:03’); -> 200907 EXTRACT(MICROSECOND FROM ‘2003-01-02 10:30:00.000123’); -> 123 |
| LAST_DAY() | 月份的最后一天 | SELECT LAST_DAY(‘2003-02-05’); -> ‘2003-02-28’ |
| DAYOFWEEK() | 星期几,1-星期日 | SELECT DAYOFWEEK(‘2019-01-21’); -> 2, 星期一 |
| DAYOFYEAR() | 一年中的第几天 | SELECT DAYOFYEAR(‘2007-02-03’); -> 34 |
3、日期、时间计算
| 函数 | 功能 | DEMO |
|---|---|---|
| DATE_ADD() ADDDATE() | 日期相加 | DATE_ADD(‘2008-01-02’, INTERVAL 31 DAY); -> '2008-02-02’ ADDDATE(‘2008-01-02’, INTERVAL 31 DAY); -> ‘2008-02-02’ |
| ADDTIME() | 时间相加 | SELECT ADDTIME(‘2007-12-31 23:59:59.999999’, ‘1 1:1:1.000002’); -> '2008-01-02 01:01:01.000001’ SELECT ADDTIME(‘01:00:00.999999’, ‘02:00:00.999998’); -> ‘03:00:01.999997’ |
| SUBDATE(date,INTERVAL expr unit) SUBDATE(expr,days) DATE_SUB同SUBDATE | 日期相减 | SELECT DATE_SUB(‘2008-01-02’, INTERVAL 31 DAY); -> '2007-12-02’ SELECT SUBDATE(‘2008-01-02’, 31); -> ‘2007-12-02’ |
| SUBTIME(expr1,expr2) | 时间相减 | SELECT SUBTIME(‘01:00:00.999999’, ‘02:00:00.999998’); -> ‘-00:59:59.999999’ |
| DATEDIFF(expr1,expr2) | 天数相减 | DATEDIFF(‘2010-11-30 23:59:59’,‘2010-12-31’); -> -31 |
| TIMEDIFF(expr1,expr2) | 时间相减 | SELECT TIMEDIFF(‘2008-12-31 23:59:59.000001’,‘2008-12-30 01:01:01.000002’); -> ‘46:58:57.999999’ |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 返回日期相减部分 | TIMESTAMPDIFF(MONTH,‘2003-02-01’,‘2003-05-01’); -> 3 |
4、日期、时间格式化函数
DATE_FORMAT(date,format)， Formats the date value according to the format string. 常见格式如下表：
| Specifier | Description |
|---|---|
| %a | Abbreviated weekday name (Sun…Sat) |
| %b | Abbreviated month name (Jan…Dec) |
| %c | Month, numeric (0…12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, numeric (00…31) |
| %e | Day of the month, numeric (0…31) |
| %f | Microseconds (000000…999999) |
| %H | Hour (00…23) |
| %h | Hour (01…12) |
| %i | Minutes, numeric (00…59) |
| %j | Day of year (001…366) |
| %M | Month name (January…December) |
| %m | Month, numeric (00…12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Seconds (00…59) |
| %s | Seconds (00…59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %W | Weekday name (Sunday…Saturday) |
| %w | Day of the week (0=Sunday…6=Saturday) |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
mysql> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T'); -> '2019-01-21 21:20:15'
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00'
5、日期、时间 单位 常量列表
ADDDATE(date,INTERVALexprunit), SUBDATE(date,INTERVAL expr unit)等函数用到的日期、时间单位：
MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR
SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND
HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND
DAY_MINUTE、DAY_HOUR、YEAR_MONTH
二、日期、时间函数常见使用场景
1、获取当前时间
创建订单时需要创建时间，通常用now()来表示，如：
insert into sale_orders(......,create_date) values (......, now());
2、日期格式化
如订单创建时间datetime类型，格式化成 年-月-日 时:分:秒 或 两位年-月-日 时:分
mysql> SELECT DATE_FORMAT(create_date,'%Y-%m-%d %T') from ...; -> '2019-01-21 21:20:15'
mysql> SELECT DATE_FORMAT(create_date,'%y-%m-%d %H:%i') from ...; -> '19-01-21 21:20'
3、日期加、减
日常开发中，查询条件经常会有开始截止日期，如查询2019-01-01到今天的创建的订单（创建时间为datetime类型），sql如下：
select * from sale_orders where create_date >= '2019-01-01' and create_date < date_add(now(), INTERVAL 1 day)
4、一个经典的案例，根据出生日期计算年龄（周岁）
假设出生日期为：1986-03-05，则年龄计算如下：
SELECT TIMESTAMPDIFF(YEAR, '1986-03-05', CURDATE()) -> 33
三、根据当前时间戳自动更新
数据库中很多表都有类似最后操作人员、操作时间字段，关于最后操作时间我们当然可以在应用中通过sql insert 或 update来实现，除此之外我们可以mysql数据库自带的“根据当前时间戳更新”功能自动更新“最后操作时间”，字段列定义如下：
...`write_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,...
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！