MySQL函数之日期和时间函数

1. 获取当前日期的函数和获取当前时间的函数

/*获取当前日期的函数和获取当前时间的函数，将日期以'YYYY-MM-DD'或者’YYYYMMDD’格式返回

*/

#CURDATE()与CURRENT_DATE()作用相同,返回相同的系统当前日期

#CURDATE()+0则将当前日期值转换成数值类型

SELECT CURDATE(), CURRENT_DATE(), CURDATE()+0;

 

/* CURTIME()和CURRENT_TIME()作用相同，将当前时间以'HH:MM:SS'或者’HHMMSS’格式返回

*/

#CURRENT_TIME()+0表示以数值形式HHMMSS格式显示

SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME()+0;

 

 

2. 获取当前日期与时间的函数

#从结果可以看出，下面四个函数的功能相同

SELECTCURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();

 

 

3. 获取月份的函数

#MONTH(date):函数返回date对应的月份，范围从1~12

#MONTHNAME(date):函数返回日期date对应月份的英文全名，如March

SELECT MONTH(NOW()), MONTHNAME(NOW());

 

 

4. 获取星期的函数

#DAYNAME(d):返回d对应的工作日的英文名称，如Sunday, Monday等

#DAYOFWEEK(d):返回d对应的一周中的索引（位置）。1表示周日，2表示周一，...,7表示周六

#WEEKDAY(d):返回d对应的工作日的索引。0表示周一，1表示周二，...6表示周日

SELECT DAYNAME(NOW()), DAYOFWEEK(NOW()), WEEKDAY(NOW());

 

 

5. 获取星期数的函数

#WEEK(d):计算日期d是一年中的第几周。

#WEEK(d,mode):指定mode是否起始于周日或者周一,若mode被省略，则使用default_week_format系统自变量的值0

#WEEK函数中Mode参数取值

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

本年度中有一个周一

 

#WEEKOFYEAR(d):计算某天d位于一年中的第几周，范围是1~53。相当于WEEK(d,3)

SELECT WEEK(NOW()), WEEK(NOW(),0), WEEK(NOW(),1), WEEKOFYEAR(NOW()), WEEK(NOW(),3);

 

 

6. 获取天数函数

#DAYOFYEAR(d):返回d是一年中的第几天，范围从1~366

#DAYOFMONTH(d):返回d是一个月中的第几天，范围是1~31

SELECT DAYOFYEAR(NOW()), DAYOFMONTH(NOW());

 

 

7. 获取年份、季度、月、小时、分钟和秒钟的函数

#YEAR(date):返回date对应的年份，范围是1970~2069

#QUARTER(date):返回date对应的一年中的季度，范围1~4

#MONTH(date):函数返回date对应的月份，范围从1~12

#MONTHNAME(date):函数返回日期date对应月份的英文全名，如March

#HOUR(time):返回time对应的小时数，范围是0~24

#MINUTE(time):返回time对应的分钟数，范围是从0~59

#SECOND(time):返回time对应的秒数，范围是0~59

SELECT

YEAR(CURDATE()) as yr,

QUARTER(CURDATE()) as qt,

MONTH(CURDATE()) as mon,

MONTHNAME(CURDATE()) as mon_name,

HOUR(CURTIME()) as hrs,

MINUTE(CURTIME()) as min,

SECOND(CURTIME()) as snd;

 

 

8. 获取日期的指定值函数EXTRACT(type FROM date)

#用于从date中按照type提取一部分值

SELECT

EXTRACT(YEAR FROM NOW()) as year,

EXTRACT(QUARTER FROM NOW()) as quarter,

EXTRACT(MONTH FROM NOW()) as month,

EXTRACT(DAY FROM NOW()) as day,

EXTRACT(HOUR FROM NOW()) as hour,

EXTRACT(MINUTE FROM NOW()) as minute,

EXTRACT(SECOND FROM NOW()) as second,

EXTRACT(YEAR_MONTH FROM NOW()) as year_mon

;

 

 

9. 时间和秒钟转换函数TIME_TO_SEC(time)

#转换公式:<小时*3600+分钟*60+秒>

#TIME_TO_SEC(time):返回已转化成秒的time参数

#SEC_TO_TIME(seconds):返回被转化为小时、分钟和秒数的seconds参数值

SELECT 

CURTIME() as time, 

TIME_TO_SEC(CURTIME()) as second,

SEC_TO_TIME(TIME_TO_SEC(CURTIME())) as time;

 

10. 根据format字符串格式化date值DATE_FORMAT(date,format)

下列修饰符可以被用在format字符串中：%M月名字(January……December)%W星期名字(Sunday……Saturday)%D有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）%Y年, 数字, 4 位%y年, 数字, 2 位%a缩写的星期名字(Sun……Sat)%d月份中的天数, 数字(00……31)%e月份中的天数, 数字(0……31)%m月, 数字(01……12)%c月, 数字(1……12)%b缩写的月份名字(Jan……Dec)%j一年中的天数(001……366)%H小时(00……23)%k小时(0……23)%h小时(01……12)%I小时(01……12)%l小时(1……12)%i分钟, 数字(00……59)%r时间,12 小时(hh:mm:ss [AP]M)%T时间,24 小时(hh:mm:ss)%S秒(00……59)%s秒(00……59)%pAM或PM%w一个星期中的天数(0=Sunday ……6=Saturday ）%U星期(0……52), 这里星期天是星期的第一天%u星期(0……52), 这里星期一是星期的第一天%%一个文字“%”。

所有的其他字符不做解释被复制到结果中。

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

    -> 'Saturday October 1997' 

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); 

    -> '22:23:00' 

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); 

    -> '4th 97 Sat 04 10 Oct 277' 

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'

MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。

TIME_FORMAT(time,format)

这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。其他修饰符产生一个NULL值或0。

11. 日期运算  

DATE_ADD(date,INTERVAL expr type)

　

DATE_SUB(date,INTERVAL expr type)

　

ADDDATE(date,INTERVAL expr type)

　

SUBDATE(date,INTERVAL expr type)

这些功能执行日期运算。对于MySQL 3.22，他们是新的。ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的同义词。

在MySQL 3.23中，你可以使用+和-而不是DATE_ADD()和DATE_SUB()。

（见例子）date是一个指定开始日期的DATETIME或DATE值，expr是指定加到开始日期或从开始日期减去的间隔值一个表达式，expr是一个字符串；它可以以一个“-”开始表示负间隔。type是一个关键词，指明表达式应该如何被解释。EXTRACT(type FROM date)函数从日期中返回“type”间隔。下表显示了type和expr参数怎样被关联：type值含义期望的expr格式SECOND秒SECONDSMINUTE分钟MINUTESHOUR时间HOURSDAY天DAYSMONTH月MONTHSYEAR年YEARSMINUTE_SECOND分钟和秒"MINUTES:SECONDS"HOUR_MINUTE小时和分钟"HOURS:MINUTES"DAY_HOUR天和小时"DAYS HOURS"YEAR_MONTH年和月"YEARS-MONTHS"HOUR_SECOND小时, 分钟，"HOURS:MINUTES:SECONDS"DAY_MINUTE天, 小时, 分钟"DAYS HOURS:MINUTES"DAY_SECOND天, 小时, 分钟, 秒"DAYS HOURS:MINUTES:SECONDS"

MySQL在expr格式中允许任何标点分隔符。表示显示的是建议的分隔符。如果date参数是一个DATE值并且你的计算仅仅包含YEAR、MONTH和DAY部分(即，没有时间部分)，结果是一个DATE值。否则结果是一个DATETIME值。

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 

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

mysql> SELECT INTERVAL 1 DAY + "1997-12-31"; 

    -> 1998-01-01 

mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND; 

    -> 1997-12-31 23:59:59 

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 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

如果你指定太短的间隔值(不包括type关键词期望的间隔部分)，MySQL假设你省掉了间隔值的最左面部分。例如，如果你指定一个type是DAY_SECOND，值expr被希望有天、小时、分钟和秒部分。如果你象"1:10"这样指定值，MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说，"1:10" DAY_SECOND以它等价于"1:10" MINUTE_SECOND的方式解释，这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性。如果你使用确实不正确的日期，结果是NULL。如果你增加MONTH、YEAR_MONTH或YEAR并且结果日期大于新月份的最大值天数，日子在新月用最大的天调整。

mysql> select DATE_ADD('1998-01-30', Interval 1 month); -> 1998-02-28

注意，从前面的例子中词INTERVAL和type关键词不是区分大小写的。