日期和时间函数
日期和时间函数主要用来处理日期和时间值,一般的日期函数除了使用date类型的参数外,也使用datetime或者timestamp类型的参数,但会忽略这些值的时间部分。相同的,以time类型值为参数的函数,可以接受timestamp类型的参数,但会忽略日期部分,许多日期函数可以同时接受数和字符串类型两种参数。
1.获取当前日期的函数和获取当前时间的函数
curdate()和current_date()函数作用相同,将当前日期按照'YYYY-MM-DD'或'YYYYMMDD'格式的值返回,具体格式根据函数在字符串或数字语境而定。
案例一:使用日期函数获取系统当前日期,SQL语句如下:
SELECT CURDATE(), CURRENT_DATE(), CURDATE() + 0;
案例二:使用时间函数获取系统当前时间,SQL语句如下:
SELECT CURTIME(), CURRENT_TIME(), CURTIME() + 0;
2.获取当前日期和时间函数
current_timestamp(),localtime(),now(),sysdate()4个函数的作用相同,均返回当前日期和时间值,格式为'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS',具体格式根据函数在字符串或数字语境中而定。
案例:使用日期时间函数获取当前系统日期和时间,SQL语句如下:
SELECT CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE();
3.unix时间戳函数
a.unix_timestamp(date)函数,若无参数调用,则返回一个unix时间戳('1970-01-01 00:00:00'GMT之后的秒数)作为无符号整数。其中,GMT(Greenwich mean time)为格林尼治标准时间。若date来调用unix_timestamp(),它会将参数以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date可以是一个date字符串、datetime字符串、timestamp或一个当地时间的YYMMDD或YYYYMMDD格式的数字。
案例:使用unix_timestamp函数返回unix格式的时间戳,SQL语句如下:
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
b.from_unixtime(date)函数把unix时间戳转换为普通格式的时间,与unix_timestamp(date)函数互为反函数。
案例:使用from_unixtime函数将unix时间戳转换为普通格式的时间,SQL语句如下:
SELECT FROM_UNIXTIME('1466393937');
4.返回utc日期的函数和返回utc时间的函数
a.utc_date()函数返回当前utc(世界标准时间)日期值,其格式为'YYYY-MM-DD'或'YYYYMMDD',具体格式取决于函数是否在用字符串或数字语境中。
案例:使用utc_date()函数返回当前utc日期值,SQL语句如下:
SELECT UTC_DATE(), UTC_DATE() + 0;
(注:utc_date()函数返回值为当前时区的日期值。)
b.utc_time()函数返回当前utc时间值,其格式为'HH:MM:SS'或'HHMMSS',具体格式取决于函数是否用在字符串或数字语境中。
案例:使用utc_time()函数返回当前utc时间值,SQL语句如下:
SELECT UTC_TIME(), UTC_TIME() + 0;
(注:utc_time()函数返回值为当前时区的时间值。)
5.获取月份的函数month(date)和monthname(date)
a.month(date)函数返回date对应的月份,范围从1~12。
案例:使用month()函数返回指定日期中的月份,SQL语句如下:
SELECT MONTH('2016-02-13');
b.monthname(date)函数返回日期date对应月份的英文全名。
案例:使用monthname()函数返回指定日期中的月份的名称,SQL语句如下:
SELECT MONTHNAME('2016-02-13');
6.获取星期的函数dayname(d)、dayofweek(d)和weekday(d)
a.dayname(d)函数返回d对应的工作日的英文名称,例如Sunday、Monday等。
案例:使用dayname()函数返回指定日期的工作日名称,SQL语句如下:
SELECT DAYNAME('2016-02-13');
b.dayofweek(d)函数返回d对应的一周中的索引(位置)。1表示周日,2表示周一,...,7表示周六。
案例:使用dayofweek()函数返回日期对应的周索引,SQL语句如下:
SELECT DAYOFWEEK('2016-02-13');
c.weekday(d)返回d对应的工作日索引。0表示周一,1表示周二,...,6表示周日。
案例:使用weekday()函数返回日期对应的工作日索引,SQL语句如下:
SELECT WEEKDAY('2016-02-14 22:23:00'), WEEKDAY('2016-04-01');
(注:可以看到weekday()和dayofweek()函数都是返回指定日期在某一周内的位置,只是索引编号不同。)
7.获取星期数的函数week(d)和weekofyear(d)
a.week(d)计算日期d是一年中的第几周。week()的双参数形式允许指定该星期是否起始于周日或周一,以及返回值的范围是否从0~53或从1~53.若mode参数被省略,则使用defaut_week_format系统自变量的值,可参考下表:
Mode | 一周的第一天 | 范围 | week1为第一周... |
周日 | 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语句如下:
SELECT WEEK('2011-02-20'), WEEK('2011-02-20', 0), WEEK('2011-02-20', 1);
b.weekofyear(d)计算某天位于一年中第几周,范围是从1~53。相当于week(d,3)。
案例:使用weekofyear()查询指定日期是一年中第几周,SQL语句如下:
SELECT WEEK('2011-02-20'), WEEKOFYEAR('2011-02-20');
8.获取天数的函数dayofyear(d)和dayofmonth(d)
a.dayofyear(d)函数返回d是一年中的第几天,范围是从1~365。
案例:使用dayofyear()函数返回指定日期在一年中的位置,SQL语句如下:
SELECT DAYOFYEAR('2016-02-20');
b.dayofmonth(d)函数返回d是一个月中的第几天,范围是从1~31。
案例:使用dayofmonth()函数返回指定日期在一个月中的位置,SQL语句如下:
SELECT DAYOFMONTH('2016-02-20');
9.获取年份、季度、小时、分钟和秒钟的函数
a.year(date)返回date对应的年份,范围是从1970~2069。
案例:使用year()函数返回指定日期对应的年份,SQL语句如下:
SELECT YEAR('11-02-03'), YEAR('96-02-03');
b.quarter(date)返回date对应的一年中的季度,范围是从1~4。
案例:使用quarter()函数返回指定日期对应的季度,SQL语句如下:
SELECT QUARTER('16-04-01');
c.minute(time)返回time对应的分钟数,范围是从0~59。
案例:使用minute()函数返回指定时间的分钟值,SQL语句如下:
SELECT MINUTE('16-02-03 10:10:03');
d.second(time)返回time对应的秒数,范围是从0~59。
案例:使用second()函数返回指定时间的秒值,SQL语句如下:
SELECT SECOND('10:05:03');
10.获取日期的指定值的函数extract(type from date)
extract(type from date)函数所使用的时间间隔类型说明符同date_add()或date_sub()的相同,但它从日期中提取一部分,而不是执行日期运算。
案例:使用extract函数提取日期或者时间值,SQL语句如下:
SELECT EXTRACT(YEAR FROM '2016-07-02') AS col11, EXTRACT(YEAR_MONTH FROM '2016-07-12 01:02:03') AS col2, EXTRACT(DAY_MINUTE FROM '2016-07-12 01:02:03') AS col3;
11.时间和秒钟转换的函数
a.time_to_sec(time)返回已转化为秒的time参数。转换公式为:小时×3600+分钟*60+秒。
案例:使用time_to_sec()函数将时间值转换为秒值,SQL语句如下:
SELECT TIME_TO_SET('23:23:00');
b.sec_to_time(seconds)返回被转化为小时、分钟和秒数的seconds参数值,其格式为'HH:MM:SS'或'HHMMSS',具体格式根据该函数是否用在字符串或数字语境而定。
案例:使用sec_to_time()函数将秒值转换为时间格式,SQL语句如下:
SELECT SEC_TO_TIME(2345), SEC_TO_TIME(2345) + 0, TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);
(注:可以看到,set_to_time()函数加上0值后变成了小数值;time_to_sec正好和set_to_time互为反函数。)
12.计算日期和时间的函数
计算日期和时间的函数有date_add()、adddate()、date_sub()、subdate()、addtime()、subtime()和date_diff()。
a.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值,计算只会包括type、month和day部分(即没有时间部分),其结果是一个date值,否则,结果将是一个datetime值。
b.date_add(date,interval expr type)和adddate(date,interval expr type)两个函数作用相同,执行日期的加运算。
案例:使用date_add()和adddate()函数执行日期加运算,SQL语句如下:
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col1, ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col2, DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;
c.date_sub(date,interval expr type)和subdate(date,interval expr type)两个函数作用相同,执行日期的减运算。
案例:使用date_sub()和subdate()函数执行日期减运算,SQL语句如下:
SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1, SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2, DATE_SUB('2011-01-01 00:01:00', INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
(注:date_add和date_sub在指定修改的时间段时,也可以指定负值,负值代表相减,即返回以前的日期和时间。)
d.addtime(date,expr)函数将expr值添加到date,并返回修改后的值,date是一个日期或者日期时间表达式,而expr是一个时间表达式。
案例:使用addtime()函数进行时间加操作,SQL语句如下:
SELECT ADDTIME('2000-12-31 23:59:59', '1:1:1'); ADDTIME('02:02:02', '02:00:00');
e.subtime(date,expr)函数中date减去expr值,并返回修改后的值,date是一个日期或者日期时间表达式,而expr是一个时间表达式。
案例:使用subtime()函数执行减操作,SQL语句如下:
SELECT SUBTIME('2000-12-31 23:59:59', '1:1:1'),SUBTIME('02:02:02', '02:00:00');
f.datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。
案例:使用datediff()函数计算两个日期之间的间隔天数,SQL语句如下:
SELECT DATEDIFF('2010-12-31 23:59:59', '2010-12-30') AS col1, DATEDIFF('2010-11-30 23:59:59', '2010-12-31') AS col2;
13.将日期和时间格式化的函数
a.date_form(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(00...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_form()函数格式化输出日期和时间值,SQL语句如下:
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1, DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j') AS col2;
b.time_format(time,format)根据format字符串安排time值的格式,format字符串可能仅会处理包含小时、分钟和秒钟的格式说明符,其他说明符产生一个null值或0,若time值包含一个大于23的小时部分,则%H和%k小时格式说明符会产生一个大于0.23的通常范围的值。
案例:使用time_format()函数格式化输入时间值,SQL语句如下:
SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
c.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语句如下:
SELECT GET_FORMAT(DATE, 'EUR'), GET_FORMAT(DATE, 'USA');
案例二:在date_format()函数中,使用get_format()函数返回显示格式字符串来显示指定的日期值,SQL语句如下:
SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE, 'USA'));