SQL日期函数

日期函数
    当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。
     下表列出了 MySQL 中最重要的内建日期函数：
     函数描述
     -----------------------------------------------------------------
      NOW()  返回当前的日期和时间
      CURDATE() 返回当前的日期
      CURTIME() 返回当前的时间
      DATE()  提取日期或日期/时间表达式的日期部分
      EXTRACT() 返回日期/时间按的单独部分
      DATE_ADD() 给日期添加指定的时间间隔
      DATE_SUB() 从日期减去指定的时间间隔
      DATEDIFF() 返回两个日期之间的天数
      DATE_FORMAT() 用不同的格式显示日期/时间
      ------------------------------------------------------------------
     日期函数用于调整日期和时间数据的外观，以适当的方式显示日期和时间数据、进行比较、计算日期之间的间隔等。
一、格式转化
    1、DATE_FORMAT 函数
        语法： 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)
  %T  时间 , 24小时制 (小时hh:分钟mm:秒数ss)
  %u  周 (00..53), 其中周一为每周的第一天 
  %v  周 (01..53), 其中周一为每周的第一天 ; 和 %x同时使用
  %W  工作日名称 (周日..周六)
  %w  一周中的每日 (0=周日..6=周六)
  %x  该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用
  %Y  年份, 数字形式，4位数
  %y  年份, 数字形式，2位数
  ------------------------------------------------------------------------------------------ 
  例：
  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'
  mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
 '1998 52'
  2、STR_TO_DATE 函数
  语法：STR_TO_DATE(str,format) 
  描述：这是DATE_FORMAT() 函数的倒转。若格式字符串 str 包含日期和时间部分，则返回一个 DATETIME 值， 若 str 只包含日期部分或时间部分，则返回一个 DATE 或 TIME 值。str 所包含的日期、时间或日期时间值应该在 format 指示的格式中被给定。若 str 包含一个非法日期、时间或日期时间值，则返回NULL。
  mysql>  SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
  '0000-00-00'
  mysql>  SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
 '2004-04-31'
二、取得日期时间差
        DATEDIFF() 返回两个日期之间的天数。
        语法：DATEDIFF(expr,expr2)
        描述：返回起始时间 expr 和结束时间 expr2 之间的天数。Expr 和expr2 为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。
    mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
  1
    mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
  -31
三、日期的加减运算
    1、给日期添加指定的时间间隔 DATE_ADD()  
       语法：DATE_ADD(date, INTERVAL expr type) 
       描述：执行日期加法运算。 date 是 DATETIME 或 DATE 值，用来指定起始时间。expr 是一个表达式，用来指定从起始日期添加的时间间隔值。
  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_ADD('1998-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
 '1997-12-30 14:00:00'
  mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
 '1993-01-01 00:00:01.000001'
    2、从日期减去指定的时间间隔 DATE_SUB() 
        语法：DATE_SUB(date,INTERVAL expr type)
        描述：执行日期减法运算。 date 是 DATETIME 或 DATE 值，用来指定起始时间。expr 是一个表达式，用来指定从起始日期减去的时间间隔值。
  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_SUB('1998-01-02', INTERVAL 31 DAY);
 '1997-12-02'
       假如你对一个日期值添加或减去一些含有时间部分的内容，则结果自动转化为一个日期时间值：
  mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
 '1999-01-02'
  mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
 '1999-01-01 01:00:00'
        假如你使用了格式严重错误的日期，则结果为 NULL。
        假如你添加了 MONTH、YEAR_MONTH 或 YEAR，而结果日期中有一天的日期大于添加的月份的日期最大限度，则这个日期自动被调整为添加月份的最大日期。
 mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
 '1998-02-28'
四、取得部分日期信息
    1、提取日期部分 DATE() 
        语法：DATE(expr) 
        描述：提取日期或时间日期表达式expr中的日期部分。
  mysql> SELECT DATE('2003-12-31 01:02:03');
 '2003-12-31'
    2、返回日期/时间值的单独部分 EXTRACT()
        语法：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
    3、日期描述
       日期描述由格式元素组成，用于以期望的格式提取日期和时间信息。
      （1）QUARTER(date) 
       返回date 对应的一年中的季度值，范围是从 1到 4。
  mysql> SELECT QUARTER('98-04-01');
 2
        （2）MONTH(date)
        返回 date 对应的月份，范围时从 1 到 12。
  mysql> SELECT MONTH('1998-02-03');
 2
        （3）MONTHNAME(date)
        返回date 对应月份的全名。
  mysql> SELECT MONTHNAME('1998-02-05');
 'February '
        （4）HOUR(time)
        返回time 对应的小时数。对于小时数的返回值范围是从 0 到 23 。
  mysql> SELECT HOUR('10:05:03');
 10
        （5）MINUTE(time)
        返回 time 对应的分钟数,范围是从 0 到 59。
  mysql> SELECT MINUTE('98-02-03 10:05:03');
 5
        （6）SECOND(time)
        返回time 对应的秒数, 范围是从 0到59。
  mysql> SELECT SECOND('10:05:03');
 3
五、其他日期函数
    1、DAYOFYEAR(date)
        返回date 对应的一年中的天数，范围是从 1到366。
  mysql> SELECT DAYOFYEAR('1998-02-03');
 34
    2、DAYOFMONTH(date)
        返回date 对应的该月日期，范围是从 1到31。
  mysql> SELECT DAYOFMONTH('1998-02-03');
 3
    3、DAYOFWEEK(date)
        返回date (1 = 周日, 2 = 周一, ..., 7 = 周六)对应的工作日索引。
  mysql> SELECT DAYOFWEEK('1998-02-03');
 3
    4、DAYNAME(date)
        返回date 对应的工作日名称。
  mysql> SELECT DAYNAME('1998-02-05');
 '周四'