您当前的位置: 站长圈 > 技术文章 > 数据库技术 > MySQL函数——日期和时间函数

MySQL函数——日期和时间函数

来源:站长圈 作者:adminzzq 点击: 0
1、获取当前日期的函数和获取当前时间的函数
CURDATE()和CURRENT_DATE()函数作用相同,将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
例:使用日期函数获取系统当期日期,输入SQL语句如下:
\
执行后得:
\
“CURDATE()+0”将当前日期值转换为数值型。
CURTIME()和CURRENT_TIME()函数作用相同,将当前时间以“HH:MM:SS”或“HHMMSS”格式返回,具体格式根据函数用在字符串或是数字语境中而定。
例:使用时间函数获取系统当期日期,输入SQL语句如下:
\
执行后得:
\
“CURTIME()+0”将当前日期值转换为数值型。
2、获取当前日期和时间的函数
CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()4个函数的作用相同,均返回当前日期和时间值,格式为:“YYYY-MM-DD HH:MM:SS”或“YYYYMMDD HHMMSS”,具体格式根据函数用在字符串或是数字语境中而定。
例:使用日期时间函数获取当前系统日期和时间,输入SQL语句如下:
\
执行后得:
\
3UNIX时间戳函数
UNIX_TIMESTAMP(date)返回一个Unix时间戳(‘1970-01-01 00:00:00’GMT 之后的秒数)作为无符号整数。其中,GMT(Green wich mean time,为格林尼治标准时间)。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。
例:使用UNIX_TIMESTAMP函数返回UNIX格式的时间戳,输入SQL语句如下:
\
执行后得:
\
FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间,与UNIX_TIMESTAMP(date)函数互为反函数。
例:使用FROM_UNIXTIME函数将UNIX时间戳转换为普通格式时间,输入SQL语句如下:
\
执行后得:
\
4、返回UTC日期的函数和返回UTC时间的函数
UTC_DATE()函数返回当前UTC(世界标准时间)日期值,其格式为“YYYY-MM-DD”或“YYYYMMDD”,具体格式根据函数用在字符串或是数字语境中而定。
例:使用UTC_DATE()函数返回当前UTC日期值,输入SQL语句如下:
\
执行后得:
\
UTC_TIME()返回当前UTC时间值,其格式为“HH:MM:SS”或“HHMMSS”,具体格式根据函数用在字符串或是数字语境中而定。
例:使用UTC_TIME()函数返回当前UTC时间值,输入SQL语句如下:
\
执行后得:
\
5、获取月份的函数MONTH(date)MONTHNAME(date)
MONTH(date)函数返回date对应的月份,范围值为1~12.
例:使用MONTH()函数返回指定日期中的月份,输入SQL语句如下:
\
执行后得:
\
MONTHNAME()函数返回日期date对应月份的英文全名。
例:使用MONTHNAME()函数返回指定日期中的月份的名称,输入SQL语句如下:
\
执行后得:
\
 6、获取星期的函数DAYNAME(d)DAYOFWEEK(d)WEEKDAY(d)
DAYNAME(d)函数返回d对应的工作日的英文名称,例如Sunday、Monday等。
例:使用DAYNAME()函数返回指定日期的工作日名称,输入SQL语句如下:
\
执行后得:
\
DAYOFWEEK(d)函数返回d对应的一周中的索引(位置)。1表示周日,2表示周一,……,7表示周六。
例:使用DAYOFWEEK()函数返回日期对应的周索引,输入SQL语句如下:
\
执行后得:
\
WEEKDAY(d)返回d对应的工作日索引。0表示周一,1表示周二,……,6表示周日。
例:使用WEEKDAY()函数返回日期对应的周索引,输入SQL语句如下:
\
执行后得:
\
可以看到WEEKDAY()和DAYOFWEEK()函数都是返回指定日期在某一周内的位置,只是索引号不同。
7、获取星期数的函数WEEK(d)WEEKOFYEAR(d)
WEEK(d)计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一,以及返回值的范围是否从0~53或1~53。若mode参数被省略,则使用default_week_format系统自变量的值,可参考表1
Mode 一周的第一天 范围 Week 1 为第一周
0 周日 0~53 本年度中的一个周日
1 周一 0~53 本年度中有3天以上
2 周日 1~53 本年度中的一个周日
3 周一 1~53 本年度中有3天以上
4 周日 0~53 本年度中有3天以上
5 周一 0~53 本年度中的一个周一
6 周日 1~53 本年度中有3天以上
7 周一 1~53 本年度中的一个周一
例:使用WEEK()函数查询指定日期是一年中的第几周,输入SQL语句如下:
\
执行后得:
\
可以看到,WEEK('2013-11-15')使用一个参数,其第二个参数为default_week_format默认值,MySQL中该默认值为0,指定一周的第一天为周日,因此与WEEK('2013-11-15',0)返回结果相同;WEEK('2013-11-15',1)中第二个参数为1,指定一周的第一天为周一,返回值为46。由此可以看到第二参数的不同,返回的结果也不同,使用不同的参数的原因是不同地区和国家的习惯不同,每周的第一天并不相同。
WEEKOFYEAR(d)计算某天位于一年中的第几周,范围是从1~53。相当于WEEK(d,3)。
例:使用WEEKOFYEAR()查询指定日期是一年中的第几周,输入SQL语句如下:
\
执行后得:
\
8、获取天数的函数DAYOFYEAR(d)DAYOFMONTH(d)
DAYOFYEAR(d)函数返回d是一年中的第几天,范围是从1~366。
例:使用DAYOFYEAR()函数返回指定日期在一年中的位置,输入SQL语句如下:
\
执行后得:
\
DAYOFMONTH(d)函数返回d是一个月中的第几天,范围是从1~31。
例:使用DAYOFMONTH()函数返回指定日期在一个月中的位置,输入SQL语句如下:
\
执行后得:
\
 
9、获取年份、季度、小时、分钟和秒钟的函数
YEAR(date)返回date对应的年份,范围是1970~2069。
例:使用YEAR()函数返回指定日期对应的年份,输入SQL语句如下:
\
执行后得:
\
QUARTER(date)返回date对应的一年中季度值,范围是1~4。
例:使用QUARTER()函数返回指定日期对应的季度,输入SQL语句如下
\
执行后得:
\
MINUTE(time)返回time对应的分钟数,范围是从0~59。
例:使用MINUTE()函数返回指定时间的分钟值,输入SQL语句如下:
\
执行后得:
\
SECOND(time)返回time对应的秒数,范围是从0~59。
例:使用SECOND()函数返回指定时间的秒值,输入SQL语句如下:
\
执行后得:
\
 
10、获取日期的指定值的函数EXTRACT(type from date)
EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同,但它从日期中提取一部分,而不是执行日期运算。
例:使用EXTRACT函数提前日期或者时间值,输入SQL语句如下:
\
执行后得:
\
type值为year时,只返回年值;为year_month时返回年与月份;为day_minute时返回日、小时和分钟值。
11、时间和秒钟转换的函数
TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为:小时*3600+分钟*60+秒。
例:使用TIME_TO_SEC函数将时间值转换为秒值,输入SQL语句如下:
\
执行后得:
\
SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值,其格式为“HH:MM:SS”或“HHMMSS”,具体格式根据函数是否用在字符串或数字语境中而定。
例:使用SEC_TO_TIME()函数将秒值转换为时间格式,输入SQL语句如下:
\
执行后得:
\
TIME_TO_SEC()正好与SEC_TO_TIME()互为反函数。
12、计算时期和时间的函数
计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUB_DATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。
DATE_ADD(date, INTERVAL expr type) 和DATE_SUB(date,INTERVAL expr type),其中,date是一个DATETIME或DATE值,用来指定起始时间。expr是一个表达式,用来指定从起始日期添加或减去的时间间隔值。expr是一个字符串;对于负值的时间间隔,它可以以一个负号“-”开头。type为关键词,它指示了表达式被解释的方式。下表显示了type和expr参数的关系:
type值 预期的expr格式
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_ MICROSECOND ‘SECONDS. MICROSECONDS’
MINUTE_ MICROSECOND ‘MINUTES.MICROSECONDS’
MINUTE_ SECOND ‘MINUTES:SECONDS’
HOUR_ MICROSECOND ‘HOURS. MICROSECONDS’
HOUR_ SECOND ‘HOURS:MINUTES: SECONDS’
HOUR_ MINUTE ‘HOURS:MINUTES’
DAY_ MICROSECOND ‘DAYS.MICROSECONDS’
DAY_ SECOND ‘DAYS  HOURS:MINUTES:SECONDS’
DAY_ MINUTE ‘DAYS  HOURS: MINUTES’
DAY _HOUR ‘DAYS  HOURS’
YEAR_ MONTH ‘YEARS_ MONTHS’
若date参数是一个DATE值,计算只会包括YEAR、MONTH和DAY部分(即没有时间部分),其结果是一个DATE值。否则,结果将是一个DATETIME值。
DATE_ADD(date,INTERVAL expr type)或者ADDDATE (date,INTERVAL expr type),两个函数作用相同,执行日期的加运算。
例:使用DATE_ADD()和ADDDATE()函数执行日期加操作,输入SQL语句如下:
\
执行后得:
\
DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1'MINUTE_SECOND )日期运算类型是MINUTE_SECOND,将指定时间增加1分1秒后返回,结果为‘2011-01-01 00:01:00’。
DATE_SUB(date,INTERVAL expr type)或者SUBDATE_(date,INTERVAL expr type) ,两个函数作用相同,执行日期的减运算。
例:使用DATE_SUB和SUBDATE函数执行日期减操作,输入SQL语句如下:
\
执行后得:
\
DATE_SUB( '2011-01-01 00:01:00', INTERVAL '0 0:1:1'DAY_SECOND )函数将指定日期减少1天,时间减少1分1秒后返回,结果为‘2010-12-31 23:59:59’。
提示:DATE_ADD和DATE_SUB在指定修改的时间段时,也可以指定负值,负值代表相减,即返回以前的日期和时间。
ADDTIME(date,expr)函数将expr值添加到date,并返回修改后的值,date是一个日期时间表达式,而expr是一个时间表达式。
例:使用ADDTIME进行时间加操作,输入SQL语句如下:
\
执行后得:
\
SUBTIME(date,expr)函数将date减去expr值,并返回修改后的值,date是一个日期或者日期时间表达式,而expr是一个时间表达式。
例:使用SUBTIME()函数执行时间减操作,输入SQL语句如下:
\
执行后得:
\
DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。
例:使用DATEDIFF()函数计算两个日期之间的间隔天数,输入SQL语句如下:
\
执行后得:
\
DATEDIFF()函数返回date1-date2后的值。
13、将日期和时间格式化的函数
DATE_FORMAT(date,format)根据format指定的格式显示date值,主要format格式如下表所示:
说明符 说明
%a 工作日的缩写名称(Sun..Sat)
%b 月份的缩写名称(Jan..Dec)
%c 月份,数字形式(0..12)
%D 带有英语后缀的该月日期(0th ,1st ,2nd ,3rd ,…)
%d 该月日期,数字形式(00..31)
%e 该月日期,数字形式(0..31)
%f 微秒(000000..999999)
%H 以2位数表示24小时(00..23)
%h, %I 以2位数表示12小时(01..12)
%i 分钟,数字形式(00..59)
%j 一年中的天数(001..366)
%k 以24小时(0..23)
%l 以12小时(1..12)
%M 月份名称(January..December)
%m 月份,数字形式(00..12)
%p 上午(AM)或下午(PM)
%r 时间,12小时制(小时hh:分钟mm:秒数ss后加AM或PM)
%S, %s 以2位数形式表示秒(00..59)
%T 时间,24小时制(小时hh:分钟mm:秒数ss)
%U 周(00..53),其中周日为每周的第一天
%u 周(00..53),其中周一为每周的第一天
%V 周(01..53),其中周日为每周的第一天:和%X同时使用
%v 周(01..53),其中周一为每周的第一天:和%X同时使用
%W 工作日名称(周日..周六)
%w 一周中的每日(0=周日..6=周六)
%X 该周的年份,其中周日为每周的第一天:数字形式,4位数;和%V同时使用
%x 该周的年份,其中周一为每周的第一天:数字形式,4位数;和%v同时使用
%Y 4位数形式表示的年份
%y 2位数形式表示的年份
%% ‘%’文字字符
例:使用DATE_FORMAT()函数格式化输出日期和时间值,输入SQL语句如下:
\
执行后得:
\
\
执行后得:
\
TIME_FORMAT(time,format)根据format字符串安排time值的格式。format字符串可能仅会处理包含小时、分钟和秒的格式说明符,其他说明符产生一个NULL值或0。若time值包含一个大于23的小时部分,则%H和%k小时格式说明符会产生一个大于0..23的通常范围的值。
例:使用TIME_FORMAT()函数格式化输入时间值,输入SQL语句如下:
\
执行后得:
\
TIME_FORMAT()只处理时间值。
GET_FORMAT(val_type,format_type)返回日期时间字符串的显示格式,val_type表示日期数据类型,包括DATE、DATETIME和TIME;format_type表示格式化显示类型,包括EUR、INTERVAL、ISO、JIS、USA。GET_FORMAT()根据两个值类型组合返回的字符串显示格式如下表所示:
值类型 格式化类型 显示格式字符串
DATE EUR %d. %m. %Y
DATE INTERVAL %Y%m%d
DATE ISO %Y-%m-%d
DATE JIS %Y-%m-%d
DATE USA %m. %d. %Y
TIME EUR %H.%i.%s
TIME INTERVAL %H%i%s
TIME ISO %H:%i:%s
TIME JIS %H:%i:%s
TIME USA %h: %i: %s %p
DATETIME EUR %Y-%m-%d %H.%i.%s
DATETIME INTERVAL %Y%m%d %H %i %s
DATETIME ISO %Y-%m-%d %H:%i:%s
DATETIME JIS %Y-%m-%d %H:%i:%s
DATETIME USA %Y-%m-%d %H.%i.%s
例:使用GET_FORMAT()函数显示不同格式化类型下的显示格式字符串,输入SQL语句如下:
\
执行后得:
\
例:在DATE_FORMAT()函数中使用GET_FORMAT函数返回的显示格式字符串来显示指定的日期值,输入SQL语句如下:
\
执行后得:
\