Mysql函数总结（3）——日期时间函数

1.ADDDATE(date,INTERVAL expr unit), ADDDATE(date,expr)
说明：ADDDATE(date,INTERVAL expr unit)与DATE_ADD()同义。
示例：
mysql>  SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2008-02-02                              |
+-----------------------------------------+
1 row in set (0.15 sec)

mysql> SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 2008-02-02                |
+---------------------------+
1 row in set (0.02 sec)

mysql>

2.ADDTIME(expr1,expr2)
说明：将expr1与expr2相加，返回结果
示例：
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997                               |
+-----------------------------------------------+
1 row in set (0.06 sec)

mysql>

3.CURDATE(), CURRENT_DATE, CURRENT_DATE()
说明：返回当前日期。
示例：
mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2013-06-15 |
+------------+
1 row in set (0.00 sec)

mysql>

4.CURRENT_TIME, CURRENT_TIME(), CURTIME()
说明：返回当前时间。
示例：
mysql> SELECT CURTIME(), CURRENT_TIME, CURRENT_TIME();
+-----------+--------------+----------------+
| CURTIME() | CURRENT_TIME | CURRENT_TIME() |
+-----------+--------------+----------------+
| 11:02:36  | 11:02:36     | 11:02:36       |
+-----------+--------------+----------------+
1 row in set (0.00 sec)

mysql>

5.CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()
说明：返回当前时间戳。
示例：
mysql> SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();
+---------------------+---------------------+
| CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP() |
+---------------------+---------------------+
| 2013-06-15 11:03:41 | 2013-06-15 11:03:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

6.DATE(expr)
说明：提取expr的日期部分。expr为date祸datetime类型。
示例：
mysql> SELECT DATE(CURRENT_TIMESTAMP());
+---------------------------+
| DATE(CURRENT_TIMESTAMP()) |
+---------------------------+
| 2013-06-15                |
+---------------------------+
1 row in set (0.00 sec)

mysql>

7.DATEDIFF(expr1,expr2)
说明：返回expr1与expr2的时间差，结果以天为单位。expr1与expr2为date或datetime类型表达式。
示例：
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.19 sec)

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
|                                          -31 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

8.DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
说明：日期加减。支持的unit类型及对应的expr表达式类型如下：
unit                         Value Expected expr Format
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:SECONDS.MICROSECONDS'
MINUTE_SECOND                 'MINUTES:SECONDS'
HOUR_MICROSECOND             'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND                 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE                 'HOURS:MINUTES'
DAY_MICROSECOND             'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND                     'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE                     'DAYS HOURS:MINUTES
DAY_HOUR                     'DAYS HOURS'
YEAR_MONTH                     'YEARS-MONTHS'
示例：
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1998-02-02                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

9.DATE_FORMAT(date,format)
说明：以format格式化date，并返回字符串表达式。支持的format格式如下：
Specifier Description
%a         Abbreviated weekday name (Sun..Sat)
%b         Abbreviated month name (Jan..Dec)
%c         Month, numeric (0..12)
%D         Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d         Day of the month, numeric (00..31)
%e         Day of the month, numeric (0..31)
%f         Microseconds (000000..999999)
%H         Hour (00..23)
%h         Hour (01..12)
%I         Hour (01..12)
%i         Minutes, numeric (00..59)
%j         Day of year (001..366)
%k         Hour (0..23)
%l         Hour (1..12)
%M         Month name (January..December)
%m         Month, numeric (00..12)
%p         AM or PM
%r         Time, 12-hour (hh:mm:ss followed by AM or PM)
%S         Seconds (00..59)
%s         Seconds (00..59)
%T         Time, 24-hour (hh:mm:ss)
%U         Week (00..53), where Sunday is the first day of the week
%u         Week (00..53), where Monday is the first day of the week
%V         Week (01..53), where Sunday is the first day of the week; used with %X
%v         Week (01..53), where Monday is the first day of the week; used with %x
%W         Weekday name (Sunday..Saturday)
%w         Day of the week (0=Sunday..6=Saturday)
%X         Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x         Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y         Year, numeric, four digits
%y         Year, numeric (two digits)
%%         A literal “%” character
%x         x, for any “x” not listed above
示例：
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+
1 row in set (0.01 sec)

mysql>

10.DAY(date), DAYOFMONTH(date)
说明：返回日期中的天部分。
示例：
mysql> SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

mysql>

11.DAYNAME(date)
说明：返回给定日期的星期的名称。
示例：
mysql> SELECT DAYNAME('2007-02-03');
+-----------------------+
| DAYNAME('2007-02-03') |
+-----------------------+
| Saturday              |
+-----------------------+
1 row in set (0.04 sec)

mysql>

12.DAYOFWEEK(date)
说明：返回给定日期的星期的数字。1为周日，7为周六
示例：
mysql> SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

mysql>

13.DAYOFYEAR(date)
说明：返回给定的日期是当年的哪一天。
示例：
mysql> SELECT DAYOFYEAR('2007-02-03');
+-------------------------+
| DAYOFYEAR('2007-02-03') |
+-------------------------+
|                      34 |
+-------------------------+
1 row in set (0.00 sec)

mysql>

14.EXTRACT(unit FROM date)
说明：从给定的日期中抽取unit部分。
示例：
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
|                                         200907 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

15.FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
说明：将unix时间戳转换为datetime格式。
示例：
mysql> SELECT FROM_UNIXTIME(1196440219);
+---------------------------+
| FROM_UNIXTIME(1196440219) |
+---------------------------+
| 2007-11-30 11:30:19       |
+---------------------------+
1 row in set (0.03 sec)

mysql>

16.HOUR(time)
说明：返回时间中的小时部分。
示例：
mysql> SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
|               10 |
+------------------+
1 row in set (0.00 sec)

mysql>

17.LAST_DAY(date)
说明：返回给定日期的当月的最后一天。
示例：
mysql>  SELECT LAST_DAY('2003-02-05'), LAST_DAY('2004-02-05');
+------------------------+------------------------+
| LAST_DAY('2003-02-05') | LAST_DAY('2004-02-05') |
+------------------------+------------------------+
| 2003-02-28             | 2004-02-29             |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql>

18.LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP(), NOW()
说明：返回当前时间。
示例：
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2013-06-15 11:35:28 |
+---------------------+
1 row in set (0.00 sec)

mysql>

19.MAKEDATE(year,dayofyear)
说明：给定一个年份以及相应的天数，返回一个日期对象。
示例：
mysql> SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31        | 2011-02-01        |
+-------------------+-------------------+
1 row in set (0.04 sec)

mysql>

20.MAKETIME(hour,minute,second)
说明：根据给定的时间返回time对象。
示例：
mysql> SELECT MAKETIME(12,15,30);
+--------------------+
| MAKETIME(12,15,30) |
+--------------------+
| 12:15:30           |
+--------------------+
1 row in set (0.00 sec)

mysql>

21.MICROSECOND(expr)
说明：返回给定时间的毫秒数。
示例：
mysql> SELECT MICROSECOND('2009-12-31 23:59:59.000010');
+-------------------------------------------+
| MICROSECOND('2009-12-31 23:59:59.000010') |
+-------------------------------------------+
|                                        10 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

22.MINUTE(time)
说明：返回给定时间的分钟部分。
示例：
mysql> SELECT MINUTE('2008-02-03 10:05:03');
+-------------------------------+
| MINUTE('2008-02-03 10:05:03') |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

23.MONTH(date)
说明：返回给定日期的月部分。
示例：
mysql> SELECT MONTH('2008-02-03');
+---------------------+
| MONTH('2008-02-03') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

mysql>

24.MONTHNAME(date)
说明：返回给定日期的月份的名称。
示例：
mysql> SELECT MONTHNAME('2008-02-03');
+-------------------------+
| MONTHNAME('2008-02-03') |
+-------------------------+
| February                |
+-------------------------+
1 row in set (0.00 sec)

mysql>

25.QUARTER(date)
说明：返回给定日期所在的季度。
示例：
mysql> SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

mysql>

26.SECOND(time)
说明：返回给定时间的秒部分。
示例：
mysql> SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

mysql>

27.STR_TO_DATE(str,format)
说明：将字符串str以format格式转换为时间。
示例：
mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
+-------------------------------------+
| STR_TO_DATE('01,5,2013','%d,%m,%Y') |
+-------------------------------------+
| 2013-05-01                          |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

28.SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
说明：SUBDATE(date,INTERVAL expr unit)与DATE_SUB同义。
示例：
mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| SUBDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2007-12-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql>

29.SUBTIME(expr1,expr2)
说明：返回expr1与expr2的时间差，结果与expr1格式相同。
mysql> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
+--------------------------------------------------------+
| SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2007-12-30 22:58:58.999997                             |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

30.SYSDATE()
说明：返回系统时间。与NOW()不同之处在于NOW返回该SQL语句执行时的时间，而SYSDATE返回当前系统的时间。
示例：
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2013-06-15 11:52:28 |        0 | 2013-06-15 11:52:28 |
+---------------------+----------+---------------------+
1 row in set (2.03 sec)

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2013-06-15 11:52:36 |        0 | 2013-06-15 11:52:38 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

mysql>

31.TIME(expr)
说明：抽取expr1中的时间部分。
示例：
mysql> SELECT TIME('2003-12-31 01:02:03.000123');
+------------------------------------+
| TIME('2003-12-31 01:02:03.000123') |
+------------------------------------+
| 01:02:03.000123                    |
+------------------------------------+
1 row in set (0.00 sec)

mysql>

32.TIMEDIFF(expr1,expr2)
说明：返回expr1与expr2的时间差。
示例：
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
+---------------------------------------------------------------+
| TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001') |
+---------------------------------------------------------------+
| -00:00:00.000001                                              |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

33.TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
说明：将expr转换为时间戳，或将expr1与expr2的和转换为时间戳。
示例：
mysql> SELECT TIMESTAMP('2003-12-31');
+-------------------------+
| TIMESTAMP('2003-12-31') |
+-------------------------+
| 2003-12-31 00:00:00     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
+---------------------------------------------+
| TIMESTAMP('2003-12-31 12:00:00','12:00:00') |
+---------------------------------------------+
| 2004-01-01 00:00:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

34.TIMESTAMPADD(unit,interval,datetime_expr), TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
说明：时间戳的加减。
示例：
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+-------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02') |
+-------------------------------------+
| 2003-01-02 00:01:00                 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') |
+------------------------------------------------+
|                                              3 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

35.TIME_TO_SEC(time)
说明：将给定的time转换为秒。
示例：
mysql> SELECT TIME_TO_SEC('22:23:00');
+-------------------------+
| TIME_TO_SEC('22:23:00') |
+-------------------------+
|                   80580 |
+-------------------------+
1 row in set (0.02 sec)

mysql>

36.TO_DAYS(date)
说明：将给定的日期转换为天数，从0年开始。
示例：
mysql> SELECT TO_DAYS('2007-10-07');
+-----------------------+
| TO_DAYS('2007-10-07') |
+-----------------------+
|                733321 |
+-----------------------+
1 row in set (0.00 sec)

mysql>

36.TO_SECONDS(expr)
说明：将给定的时间转换为秒，从0年开始。
示例：
mysql> SELECT TO_SECONDS('2009-11-29 13:43:32');
+-----------------------------------+
| TO_SECONDS('2009-11-29 13:43:32') |
+-----------------------------------+
|                       63426721412 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

37.UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
说明：返回系统当前时间戳，或将给定日期转换为时间戳。
示例：
mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2007-11-30 10:30:19');
+------------------+---------------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2007-11-30 10:30:19') |
+------------------+---------------------------------------+
|       1371312178 |                            1196436619 |
+------------------+---------------------------------------+
1 row in set (0.02 sec)

mysql>

38.WEEKDAY(date)
说明：返回给定日期的星期的数字。0为周一，6为周日。
示例：
mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
+--------------------------------+
| WEEKDAY('2008-02-03 22:23:00') |
+--------------------------------+
|                              6 |
+--------------------------------+
1 row in set (0.00 sec)

mysql>

39.WEEKOFYEAR(date)
说明：给定日期是当年的第几周。
示例：
mysql> SELECT WEEKOFYEAR('2008-02-20');
+--------------------------+
| WEEKOFYEAR('2008-02-20') |
+--------------------------+
|                        8 |
+--------------------------+
1 row in set (0.00 sec)

mysql>

40.YEAR(date)
说明：返回给定日期的年部分。
示例：
mysql> SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
|               1987 |
+--------------------+
1 row in set (0.00 sec)

mysql>

41.