mysql的日期时间函数小汇总（1）

日期或时间获取函数

CURDATE() （同义词CURRENT_DATE CURRENT_DATE()）

将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。

mysql> SELECT CURDATE();

-> '1997-12-15'

mysql> SELECT CURDATE() + 0;

-> 19971215

CURTIME() （同义词 CURRENT_TIME, CURRENT_TIME()）

将当前时间以'HH:MM:SS'或HHMMSS 的格式返回， 具体格式根据函数用在字符串或是数字语境中而定。

mysql> SELECT CURTIME();

-> '23:50:26'

mysql> SELECT CURTIME() + 0;

-> 235026

NOW()

返回当前日期和时间值，其格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS ， 具体格式取决于该函数是否用在字符串中或数字语境中。

mysql> SELECT NOW();

-> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

-> 19971215235026

在一个存储程序或触发器内, NOW() 返回一个常数时间，该常数指示了该程序或触发语句开始执行的时间。这同SYSDATE()的运行有所不同。

SYSDATE()

返回当前日期和时间值，格式为'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS， 具体格式根据函数是否用在字符串或数字语境而定。

在一个存储程序或触发器中, SYSDATE()返回其执行的时间, 而非存储成都或触发语句开始执行的时间。这个NOW()的运作有所不同。

UTC_DATE,  UTC_DATE()

返回当前UTC日期值，其格式为 'YYYY-MM-DD' 或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

-> '2003-08-14', 20030814

UTC_TIME,  UTC_TIME()

返回当前UTC 值，其格式为 'HH:MM:SS' 或HHMMSS，具体格式根据该函数是否用在字符串或数字语境而定。

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

-> '18:07:53', 180753

UTC_TIMESTAMP,  UTC_TIMESTAMP()

返回当前UTC日期及时间值，格式为'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS，具体格式根据该函数是否用在字符串或数字语境而定。

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

-> '2003-08-14 18:08:04', 20030814180804

 

日期格式化函数

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	小时(00..23)
%h	小时(01..12)
%I	小时(01..12)
%i	分钟,数字形式(00..59)
%j	一年中的天数(001..366)
%k	小时(0..23)
%l	小时(1..12)
%M	月份名称(January..December)
%m	月份, 数字形式(00..12)
%p	上午（AM）或下午（ PM）
%r	时间, 12小时制(小时hh:分钟mm:秒数ss后加 AM或PM)
%S	秒(00..59)
%s	秒(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位数)
%%	‘%’文字字符

注意， ‘%’字符要求在格式指定符之前。月份和日期说明符的范围从零开始，原因是 MySQL允许存储诸如'2004-00-00'的不完全日期.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

        -> 'Saturday October 1997'

TIME_FORMAT(time,format)

其使用和DATE_FORMAT()函数相同, 然而format字符串可能仅会包含处理小时、分钟和秒的格式说明符。其它说明符产生一个NULL值或0。

若time value包含一个大于23的小时部分，则 %H 和 %k 小时格式说明符会产生一个大于0..23的通常范围的值。另一个小时格式说明符产生小时值模数12。

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

-> '100 100 04 04 4'

STR_TO_DATE(str,format)

这是DATE_FORMAT() 函数的倒转。它获取一个字符串 str和一个格式字符串format。若格式字符串包含日期和时间部分，则STR_TO_DATE()返回一个 DATETIME 值， 若该字符串只包含日期部分或时间部分，则返回一个 DATE 或TIME值。

str所包含的日期、时间或日期时间值应该在format指示的格式中被给定。对于可用在format中的说明符，请参见DATE_FORMAT() 函数说明表。 所有其它的字符被逐字获取，因此不会被解释。若 str包含一个非法日期、时间或日期时间值，则 STR_TO_DATE()返回NULL。同时，一个非法值会引起警告。

 

日期加减函数

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL exprtype)

这些函数执行日期运算。 date 是一个 DATETIME 或DATE值，用来指定起始时间。 expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。 Expr是一个字符串;对于负值的时间间隔，它可以以一个 ‘-’开头。 type为关键词，它指示了表达式被解释的方式。

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',

-> INTERVAL 1 SECOND);

-> '1998-01-01 00:00:00'

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',

-> INTERVAL 1 DAY);

-> '1998-01-01 23:59:59'

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',

-> INTERVAL '1:1' MINUTE_SECOND);

-> '1998-01-01 00:01:00'

mysql> SELECT DATE_SUB('1998-01-01 00:00:00',

-> INTERVAL '1 1:1:1' DAY_SECOND);

-> '1997-12-30 22:58:59'

mysql> SELECT DATE_ADD('1998-01-01 00:00:00',

-> INTERVAL '-1 10' DAY_HOUR);

-> '1997-12-30 14:00:00'

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

-> '1997-12-02'

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',

-> INTERVAL '1.999999' SECOND_MICROSECOND);

-> '1993-01-01 00:00:01.000001

TIMESTAMPADD(interval,int_expr,datetime_expr)

将整型表达式int_expr添加到日期或日期时间表达式 datetime_expr中。 int_expr 的单位被时间间隔参数给定，该参数必须是以下值的其中一个： FRAC_SECOND、SECOND、MINUTE、 HOUR、 DAY、WEEK、 MONTH、 QUARTER或YEAR。

可使用所显示的关键词指定Interval值，或使用SQL_TSI_前缀。例如, DAY或SQL_TSI_DAY 都是正确的。

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

-> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

-> '2003-01-09'

TIMESTAMP(expr) ,  TIMESTAMP(expr,expr2)

对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr中，将theresult作为日期时间值返回。

mysql> SELECT TIMESTAMP('2003-12-31');

-> '2003-12-31 00:00:00'

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

-> '2004-01-01 00:00:00'

PERIOD_ADD(P,N)

添加 N 个月至周期P (格式为YYMM 或YYYYMM)，返回值的格式为YYYYMM。注意周期参数 P不是日期值。

mysql> SELECT PERIOD_ADD(9801,2);

-> 199803

SUBTIME(expr,expr2)

SUBTIME()从expr中提取expr2 ，然后返回结果。expr 是一个时间或日期时间表达式，而xpr2 是一个时间表达式。

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

-> '1997-12-30 22:58:58.999997'

mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

-> '-00:59:59.999999'

 

日期和时间统计函数

DAYOFYEAR(date)

返回date对应的一年中的天数，范围是从 1到366。下列示例为1998年开始截至1998年2月3号的天数：

mysql> SELECT DAYOFYEAR('1998-02-03');

-> 34

FROM_DAYS(N)

给定一个天数 N, 返回一个DATE值。

mysql> SELECT FROM_DAYS(729669);

-> '1997-10-07'

使用FROM_DAYS()处理古老日期时，务必谨慎。他不用于处理阳历出现前的日期(1582)。

MAKEDATE(year, dayofyear)

给出年份值和一年中的天数值，返回一个日期。dayofyear必须大于 0 ，否则结果为 NULL。

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

-> '2001-01-31', '2001-02-01'

MAKETIME(hour,minute,second)

返回由hour、 minute和second参数计算得出的时间值。

mysql> SELECT MAKETIME(12,15,30);

-> '12:15:30'

 

SEC_TO_TIME(seconds)

返回被转化为小时、 分钟和秒数的seconds参数值, 其格式为 'HH:MM:SS' 或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

TIME_TO_SEC(time)

返回已转化为秒的time参数。

mysql> SELECT TIME_TO_SEC('22:23:00');

-> 80580

TO_DAYS(date)

给定一个日期date, 返回一个天数 (从年份0开始的天数)。

mysql> SELECT TO_DAYS(950501);

-> 728779

 

日期部分提取函数

DATE(expr)

提取日期或时间日期表达式expr中的日期部分。

mysql> SELECT DATE('2003-12-31 01:02:03');

-> '2003-12-31'

TIME(expr)

提取一个时间或日期时间表达式的时间部分，并将其以字符串形式返回。

mysql> SELECT TIME('2003-12-31 01:02:03');

-> '01:02:03'

YEAR(date)

返回date对应的年份,范围是从1000到9999。

MONTH(date)

返回date对应的月份，范围时从 1 到 12。

DAY(date) （同义词 DAYOFMONTH（））

返回date对应的该月日期，范围是从 1到31。

HOUR(time)

返回time对应的小时数。对于日时值的返回值范围是从 0 到23 。

mysql> SELECT HOUR('10:05:03');

-> 10

然而, TIME 值的范围实际上非常大, 所以HOUR可以返回大于23的值。

mysql> SELECT HOUR('272:59:59');

-> 272

MINUTE(time)

返回time 对应的分钟数,范围是从0 到 59。

SECOND(time)

返回time对应的秒数, 范围是从 0到59。

MICROSECOND(expr)

从时间或日期时间表达式expr返回微秒值，其数字范围从0到 999999。

mysql> SELECT MICROSECOND('12:00:00.123456');

-> 123456

EXTRACT(type FROM date)

EXTRACT()函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同,但它从日期中提取其部分，而不是执行日期运算。

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');

-> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

-> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

-> 20102

mysql> SELECT EXTRACT(MICROSECOND

-> FROM '2003-01-02 10:30:00.00123');

-> 123