mysql 根据出生日期计算年龄、日期格式化等常见日期处理相关函数使用汇总 |
您所在的位置：网站首页 › 段嘉许的出生年月日 › mysql 根据出生日期计算年龄、日期格式化等常见日期处理相关函数使用汇总 |
|
本文分两部分，第一部分为Mysql 参考手册列举的日期、时间函数说明，第二部分为根据项目实践谈谈常用的Mysql日期、时间函数使用案例。 一、参考手册日期、时间函数说明Mysql5.7参考手册13.7 Date and Time Functions章节列举了日期、时间相关的处理函数，根据用途，我们可以把mysql日期、时间函数分为4类。 1、“常量”值输出如常见的now(), current_date()等，函数不区分大小写，具体如下表所示。 函数功能DEMONOW()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、日期时间的构造和截取 函数功能DEMOMAKEDATE()根据年份、该年份的第几天返回日期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, 2019QUARTER()获取季度SELECT QUARTER(‘2008-04-01’); -> 2MONTH()获取日期的月份SELECT MONTH(‘2008-02-03’), MONTH(NOW())2, 1DAY()DAYOFMONTH()获取月份的第几天SELECT DAYOFMONTH(‘2007-02-03’); -> 3HOUR()获取小时部分SELECT hour(‘07:02:03’),hour(now()) -> 7, 11MINUTE()获取分钟部分SELECT MINUTE(‘2008-02-03 10:05:03’); -> 5SECOND()获取秒SELECT SECOND(‘10:05:03’); -> 3MICROSECOND()获取微妙SELECT MICROSECOND(‘12:00:00.123’); -> 123EXTRACT()提取日期指定部分EXTRACT(YEAR FROM ‘2009-07-02’); -> 2009EXTRACT(YEAR_MONTH FROM ‘2009-07-02 01:02:03’); -> 200907EXTRACT(MICROSECOND FROM ‘2003-01-02 10:30:00.000123’); -> 123LAST_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、日期、时间计算 函数功能DEMODATE_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’); -> -31TIMEDIFF(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. 常见格式如下表： SpecifierDescription%aAbbreviated weekday name (Sun…Sat)%bAbbreviated month name (Jan…Dec)%cMonth, numeric (0…12)%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)%dDay of the month, numeric (00…31)%eDay of the month, numeric (0…31)%fMicroseconds (000000…999999)%HHour (00…23)%hHour (01…12)%iMinutes, numeric (00…59)%jDay of year (001…366)%MMonth name (January…December)%mMonth, numeric (00…12)%pAM or PM%rTime, 12-hour (hh:mm:ss followed by AM or PM)%SSeconds (00…59)%sSeconds (00…59)%TTime, 24-hour (hh:mm:ss)%WWeekday name (Sunday…Saturday)%wDay of the week (0=Sunday…6=Saturday)%YYear, numeric, four digits%yYear, 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 33 三、根据当前时间戳自动更新数据库中很多表都有类似最后操作人员、操作时间字段，关于最后操作时间我们当然可以在应用中通过sql insert 或 update来实现，除此之外我们可以mysql数据库自带的“根据当前时间戳更新”功能自动更新“最后操作时间”，字段列定义如下： ... `write_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ... |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |