MySQL应用总结(十三)—函数的操作(3):日期时间函数
SQL语法预览:
使用函数:【select 函数名(参数1,参数2…);】
详解:
接上期...
四、 日期和时间函数
日期和时间函数主要用来处理日期和时间值,一般的日期函数除了使用DATE类型的参数外,也可以使用DATESTAMP类型或者TIMESTAMP类型的参数,但是会忽略这些值的时间部分。相同的,以TIME类型值为参数的函数,可以接受TIMESTAMP类型的参数,但是会忽略日期部分。许多日期函数可以同时接收数和字符串这两种参数。
1. 获取当前日期的函数和获取当前时间的函数
CURDATE()函数和CURRENT_DATE()函数的作用相同,将当前日期按照按照YYYY-MM-DD或YYYYMMDD格式返回。
CURTIME()函数和CURRENT_TIME()函数的作用相同,将当前时间以HH:MM:SS或HHMMSS格式返回。
案例: 使用日期函数获取系统当前日期;使用时间函数获取当前时间,SQL语句如下:
命令语句:select CURDATE(),CURRENT_DATE(),CURDATE()+0 ;
select CURTIME(),CURRENT_TIME(),CURTIME()+0 ;
执行结果:
可以看到,两个函数的作用相同,都返回了相同的系统当前日期,“CURDATE()+0”将当前日期值转换为数值型。
可以看到,两个函数的作用相同,都返回了相同的系统当前时间。
2. 获取当前日期和时间的函数
CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()四个函数的作用相同。均返回当前日期和时间值,格式为YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS。
案例: 使用日期函数获取当前系统日期和时间,SQL语句如下:
命令语句:select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE() ;
执行结果:
可以看到,4个返回结果都是相同的。
3. UNIX时间戳函数
UNIX_TIMESTAMP()函数以UNIX时间戳函数的形式返回当前时间;UNIX_TIMESTAMP(d)函数将时间d以UNIX时间戳的形式返回。
FROM_UNIXTIME(date)函数把UNIX时间戳函数转换为普通格式的时间,与UNIX_TIMESTAMP(date)函数互为反函数。
案例: 使用UNIX_TIMESTAMP()函数返回UNIX格式的时间戳;使用FROM_UNIXTIME()函数将UNIX时间戳函数转换为普通格式时间,SQL语句如下:
命令语句:select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW(), FROM_UNIXTIME(1534058518) ;
执行结果:
可以看到,FROM_UNIXTIME()函数与UNIX_TIMESTAMP()的结果正好相反,即两个函数互为反函数。
4. 返回UTC日期的函数和返回UTC时间的函数
UTC_DATE()函数返回当前UTC(世界标准时间)日期值,其格式为YYYY-MM-DD或YYYYMMDD,具体格式取决于函数是用在字符串还是数字语境中。
UTC_TIME()返回当前的UTC时间值,其格式为HH:MM:SS或HHMMSS,具体格式取决函数是在字符串还是数字语境中。
案例: 使用UTC_DATE()函数返回当前UTC日期值;使用UTC_TIME返回当前的UTC时间值,SQL语句如下:
命令语句:select UTC_DATE(), UTC_DATE()+0 ;
select UTC_TIME(), UTC_TIME()+0 ;
执行结果:
UTC_DATE()函数的返回值为当前时区的日期值。UTC_TIME()返回当前时区的时间值。
5. 获取月份函数MONTH(date)和MONTHNAME(date)
MONTH(date)函数返回date对应月份,范围1—12。
MONTHNAME(date)函数返回date对应月份的英文全名。
案例: 使用MONTH()函数指定日期中月份;使用MONTHNAME()函数返回指定日期中的月份名称,SQL语句如下:
命令语句:select MONTH(‘2018-08-12’), MONTH(‘2020-08-12’) ;
select MONTHNAME(‘2018-08-12’),MONTHNAME(‘2018-09-01’) ;
执行结果:
6. 获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
DAYNAME(d)函数返回d对应法人工作日的英文名称,如Sunday、Monday等。
DAYOFWEEK(d)函数是返回d对应的一周中的索引(位置)。1表示周日,2表示周一...7表示周六。
案例: 使用DAYNAME()函数返回指定日期的星期数;使用DAYOFWEEK()函数返回当前日期对应的周索引,SQL语句如下:
命令语句:select DAYNAME(‘2018-08-12’), DAYOFWEEK(‘2018-08-12’) ;
执行结果:
由于2018年8月12日为周日,因此WEEKDAY()返回的结果是Sunday 而DAYOFWEEK()返回的索引值是1。可以看到,WEEKDAY()函数和DAYOFWEEK()函数都是返回指定日期在某一周内的位置,只是索引编号不同。
7. 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)
WEEK(d)函数用于计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一,以及返回值是否为0-53或1-53.若Mode参数被省略,怎使用default_week_format系统自变量的值,可参考下表:
WEEKOFYEAR(d)计算某天位与一年中的第几周,范围是1-53,相当于WEEK(d,3)
案例: 使用WEEK()和WEEKOFYEAR()函数查询指定日期是一年中的第几周,SQL语句如下:
命令语句:select WEEK(‘2018-08-12’),WEEK(‘2018-08-12’,0),WEEK(‘2018-08-12’,1);
select WEEK(‘2018-08-12’,3),WEEKOFYEAR(‘2018-08-12’) ;
执行结果:
可以看到,WEEK(‘2018-08-12’)使用了一个参数,第二个参数为默认值default_week_format,MySQL中该值默认为0,指定一周的第一天为周日,因此和WEEK(‘2018-08-12’,0)的返回结果相同;另外WEEK(‘2018-08-12’,1)和WEEK(‘2018-08-12’,3)结果相同,说明2018年1月1日是个周日或周一,一周没有过3天,所以这几个参数结果都说明这周是第32周。
8. 获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)
DAYOFYEAR(d)函数返回d是一年中的第几天,范围是1-366。
DAYOFMONTH(d)函数返回d是一个月中的第几天,范围是1-31。
案例: 使用DAYOFYEAR()函数返回指定日期在一年中的位置;使用DAYOFMONTH()函数返回指定日期在一个月中的位置,SQL语句如下:
命令语句:select DAYOFYEAR(‘2018-08-12’) , DAYOFMONTH(‘2018-08-12’)
执行结果:
9. 获取年份、季度、小时、分钟和秒钟的函数
YEAR(date)函数返回date对应的年份,范围是1970-2069。
QUARTER(date)函数返回date对应的一年中的季度值,范围1-4。
HOUR(time)函数返回time对应的时钟数,范围是0-23。
MINUTE(time)函数返回time对应的分钟数,范围是0-59。
SECOND(time)函数返回time对应的秒钟数,范围是0-59。
案例: 使用YEAR()函数返回指定日期对应的年份;使用QUARTER()函数返回指定日期对应的季度,使用HOUR()函数返回指定时间对应的时钟值,使用MINUTE()函数返回指定时间对应的分钟值,使用SECOND()函数返回指定时间对应的秒钟值,SQL语句如下:
命令语句:select YEAR(‘2018-08-12’),QUARTER(‘2018-08-12’),HOUR(‘2018-08-12 16:50:30’) ;
select MINUTE(‘2018-08-12 16:50:30’),SECOND(‘2018-08-12 16:50:30’) ;
执行结果:
10. 获取日期指定值的函数EXTRACT(type FROM date)
EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同,但它是从日期中提取一部分,而不是执行日期运算。 案例: 使用EXTRACT()函数提取日期值或时间值,SQL语句如下:
命令语句:select EXTRACT(YEAR FROM ‘2018-08-12’) as col1, EXTRACT(YEAR_MONTH FROM ‘2018-08-12’) as col2,EXTRACT(DAY_MINUTE FROM ‘2018-08-12 17:01:10’) as col3 ;
执行结果:
Type值为YEAR时只返回年值,结果为2018;type值为YEAR_MONTH时返回年月值,结果为201808;type值为DAY_MINUTE时返回日、小时和分钟值,结果为121701。
11. 时间和秒钟转换函数
TIME_TO_SEC(time)函数返回已转换为秒的time参数。转换公式为“小时*3600+分钟*60+秒”。
SEC_TO_TIME(seconds)函数返回时间,格式为HH:MM:SS或HHMMSS。
案例: 使用TIME_TO_SEC()函数将时间转换为秒值;使用SEC_TO_TIME()函数将秒数转换为时间格式,SQL语句如下:
命令语句:select TIME_TO_SEC(‘2018-08-12 17:41:50’) ;
select SEC_TO_TIME(‘63710’) ;
执行结果:
12. 计算日期和时间的函数
计算日期时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。
1).DATE_ADD(date,IINTERVAL expr type)和DATE_SUB(date,IINTERVAL expr type)
在DATE_ADD(date,IINTERVAL expr type)和DATE_SUB(date,IINTERVAL expr type)中,date是一个DATETIME或DATE值,用来指定起始时间;expr是一个表达式,用来指定从起始日期,开始添加或减去的时间间隔值,expr也是一个字符串,对于赋值的时间间隔,可以以一个负号开头;type为关键词,它表明了表达式被解释的方式。下表列出了type和expr参数的关系。
若date参数是一个date值,计算时只会包括YEAR、MONTH和DAY部分(即没有时间部分),其结果是一个DATE值。否则,结果将是一个DATETIME值。
2) DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)
DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数的作用相同,都是执行日期的加运算。
案例: 使用DATE_ADD()和ADDDATE()函数执行日期加操作,SQL语句如下:
命令语句:select DATE_ADD(‘2018-08-12 23:59:59’, INTERVAL 2 SECOND) AS col1, ADDDATE(‘2018-08-12 23:59:59’, INTERVAL 2 SECOND) AS col2, DATE_ADD(‘2018-08-12 23:59:59’, INTERVAL ‘1:1’ MINUTE_SECOND) AS col3 ;
执行结果:
3) DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)两个函数的作用相同,都是执行日期的减运算。
案例: 使用DATE_SUB()和SUBDATE()函数执行日期减操作,SQL语句如下:
命令语句: select DATE_SUB(‘2019-01-01 00:00:01’, INTERVAL 2 SECOND)AS col1, SUBDATE(‘2019-01-01 00:00:01’, INTERVAL 2 SECOND) AS col2, DATE_SUB(‘2019-01-01 00:00:01’, INTERVAL ‘0 0:1:1’ DAY_SECOND) AS col3 ;
执行结果:
4) ADDTIME(date expr)函数
ADDTIME(date expr)函数将expr值添加到date,并返回修改后的值,date是一个日期或者日期表达式,而expr是一个时间表达式。
案例: 使用ADDTIME()函数进行时间加操作,SQL语句如下:
命令语句:select ADDTIME(‘2018-12-31 23:59:59’,’’1:1:1’), ADDTIME(’01:01:01’, ’01:01:01’) ;
执行结果:
5) SUBTIME(date expr)函数
SUBTIME(date expr)函数将expr值减去到date,并返回修改后的值,date是一个日期或者日期表达式,而expr是一个时间表达式。
案例: 使用SUBTIME()函数进行时间减操作,SQL语句如下:
命令语句: select SUBTIME(‘2018-12-31 23:59:59’,’’1:1:1’), SUBTIME(’01:01:01’, ’01:01:01’) ;
执行结果:
6) DATEDIFF(date1,date2)函数
DATEDIFF(date1,date2)函数返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或者date-and-time表达式,计算中只用到这些值的日期部分。
案例: 使用DATEDIFF()函数计算两个日期之间间隔的天数,SQL语句如下:
命令语句:select DATEDIFF(‘2018-12-31 23:59:59’,’2018-01-01’), DATEDIFF(‘2018-12-31 23:59:59’,’2019-01-01’) ;
执行结果:
13. 格式化日期和时间的函数
1) DATE_FORMAT(date,format)函数。
DATE_FORMAT(date,format)函数根据format指定的格式显示date值。主要的format格式如下表所示
案例: 使用DATE_FORMAT()函数格式化输出日期和时间值,SQL语句如下:
命令语句:select DATE_FORMAT(‘2018-01-01 10:10:10’,’%W %M %Y’) AS col1, DATE_FORMAT(‘2018-01-01 10:10:10’,’%D %y %a %b %m %b %j’) AS col2, DATE_FORMAT(‘2018-01-01 10:10:10’,’%H %i %s’) AS col3, DATE_FORMAT(‘2018-01-01 10:10:10’,’%X %V’) AS col4 ;
执行结果:
2) TIME_FORMAT(time,format)函数
TIME_FORMAT(time,format)函数根据format字符串安排time值的格式。若time值包含一个大于23的小时部分,%H和%K小时格式说明会产生一个超出0—23的通常范围的值。
案例: 使用TIME_FORMAT()函数格式化输出时间值,SQL语句如下:
命令语句:select TIME_FORMAT(’20:00:00’,’%H %k %h %I %l’) ;
执行结果:
3) GET_FORMAT(val_type,format_type)函数
GET_FORMAT(val_type,format_type)函数返回日期时间字符串的显示格式,val_type表示日期数据类型,包括DATE、DATETIME和TIME;format_type表示格式化显示类型,包括EUR、INTERVAL、ISO、JIS、USA。GET_FORMAT根据两个值类型组合返回的字符串显示格式,如下表:
案例: 使用GET_FORMAT()函数显示不同格式化下的格式字符串,SQL语句如下:
命令语句:select GET_FORMAT(DATE,’EUR’),GET_FORMAT(DATE,’USA’) ;
select DATE_FORMAT(‘2018-8-12 20:50:10’,GET_FORMAT(DATE,’USA’)) ;
执行结果:
未完...待续...
SQL语法总结:
使用函数:【select 函数名(参数1,参数2…);】