mysql 日期时间处理函数

ADDDATE(date,interval expr unit)，ADDDATE(expr,days) --- 添加时间值（时区）为日期值,其为DATE_ADD()的别名

DATA_ADD(date,INTERVAL expr unit)、DATE_SUB(date,INTERVAL expr unit)

这些函数进行时间运算；date参数指定开始日期的日期或日期时间值，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'`

也可以写作

date + INTERVAL expr unitdate - INTERVAL expr unit

mysql> select '2008-1-31' + interval 1 month;+--------------------------------+| '2008-1-31' + interval 1 month |+--------------------------------+| 2008-02-29                     |+--------------------------------+1 row in set (0.00 sec)mysql> select date_add('2008-1-31',interval 1 month);+----------------------------------------+| date_add('2008-1-31',interval 1 month) |+----------------------------------------+| 2008-02-29                             |+----------------------------------------+1 row in set (0.00 sec)

获取一天的开始和最后的时间

mysql> select '2015-4-4 0:0:0' + interval 1 day - interval 1 second;+-------------------------------------------------------+| '2015-4-4 0:0:0' + interval 1 day - interval 1 second |+-------------------------------------------------------+| 2015-04-04 23:59:59                                   |+-------------------------------------------------------+1 row in set (0.00 sec)

ADDTIME(expr1,expr2) --- 增加expr2到expr1上并返回结果；expr1 是一个时间或者日期时间表达式，expr2为一个时间表达式

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');        -> '2008-01-02 01:01:01.000001'mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');        -> '03:00:01.999997'

CONVERT_TZ() Convert from one timezone to another
CURDATE() --- 返回当前的日期 YYYY-MM-DD，

mysql> select curdate();+------------+| curdate()  |+------------+| 2015-04-03 |+------------+1 row in set (0.00 sec)mysql> select curdate() + 0;+---------------+| curdate() + 0 |+---------------+|      20150403 |+---------------+1 row in set (0.00 sec)

CURRENT_DATE(), CURRENT_DATE ---- CURDATE() 的别名
CURRENT_TIME(), CURRENT_TIME --- CURTIME()的别名
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP --- NOW()的别名
CURTIME() --- 返回当前的时间

mysql> select curtime();+-----------+| curtime() |+-----------+| 14:06:30  |+-----------+1 row in set (0.00 sec)mysql> select curtime() + 0;+---------------+| curtime() + 0 |+---------------+| 140641.000000 |+---------------+1 row in set (0.00 sec)

DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT(date,format) 根据格式化字符串格式化日期

格式字符串中可以使用 '%' 说明符

常用的：

　　%a -- 星期几的缩写 (Sun..Sat)

　　%b -- 月份的缩写 (Jan...Dec)

　　%c -- 月份 (0--12)

　　%D -- 天数加上了英文后缀 (0th,1sh...)

　　%d,%e -- 月份中的天数 (0-31)

　　%H -- 24小时制 (00- 23)

　　%h,%I -- 12小时制（00-12）　　

　　%i -- 分钟 (00-59)

　　%M -- 月份 (january,december)

　　%m -- 月份 (00-12)

　　%S,%s -- 秒 (00-59)

　　%Y -- 年 4位

　　%y -- 年 2位

mysql> select date_format(now(),'%Y/%m/%d %H:%m:%s');+----------------------------------------+| date_format(now(),'%Y/%m/%d %H:%m:%s') |+----------------------------------------+| 2015/04/03 14:04:05                    |+----------------------------------------+1 row in set (0.00 sec)

DATE_SUB() Subtract a time value (interval) from a date
DATE() -- 提取日期部分

mysql> select date(now());+-----------------+| date(curdate()) |+-----------------+| 2015-04-03      |+-----------------+1 row in set (0.00 sec)

DATEDIFF(expr1,expr2) 返回expr1 - expr2之间的时间差；expr1,expr2表达式只有日期部分参与运算

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.00 sec)

DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME(unix_timestamp[,format]) 将unix时间戳格式化为日期字符串

mysql> select from_unixtime(unix_timestamp(),'%Y-%m-%d');+--------------------------------------------+| from_unixtime(unix_timestamp(),'%Y-%m-%d') |+--------------------------------------------+| 2015-04-03                                 |+--------------------------------------------+1 row in set (0.00 sec)

GET_FORMAT() Return a date format string
HOUR(time) -- 提取小时部分

mysql> select HOUR(now());+-------------+| HOUR(now()) |+-------------+|          14 |+-------------+1 row in set (0.00 sec)

LAST_DAY（date）-- 返回指定月份的最后一天。如果参数无效，则返回NULL。

mysql> select last_day('2013-2-5');+----------------------+| last_day('2013-2-5') |+----------------------+| 2013-02-28           |+----------------------+1 row in set (0.00 sec)mysql> select last_day('2004-2-5');+----------------------+| last_day('2004-2-5') |+----------------------+| 2004-02-29           |+----------------------+1 row in set (0.00 sec)mysql> select last_day('2004-2-35');+-----------------------+| last_day('2004-2-35') |+-----------------------+| NULL                  |+-----------------------+1 row in set, 1 warning (0.00 sec)

LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE(year,dayofyear) Create a date from the year and day of year
MAKETIME MAKETIME(hour,minute,second)
MICROSECOND(expr) -- 返回expr表达式的毫秒数

MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME(date) --- 返回月份的全称

mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| April            |+------------------+1 row in set (0.00 sec)

NOW() -- 返回当前的日期和时间
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() --返回秒数 (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() A synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIME(datetime expr) -- 提取表达式的时间部分
TIMEDIFF() Subtract time
TIMESTAMP（expr1[,expr2]）-- 如果是一个参数，则将expr1作为datetime返回，如果有两个参数，将第二个time类型的参数增加到expr1上，返回datetime类型

mysql> select timestamp('2015-4-8');+-----------------------+| timestamp('2015-4-8') |+-----------------------+| 2015-04-08 00:00:00   |+-----------------------+1 row in set (0.00 sec)mysql> select timestamp('2015-4-8','10:20:30');+----------------------------------+| timestamp('2015-4-8','10:20:30') |+----------------------------------+| 2015-04-08 10:20:30              |+----------------------------------+1 row in set (0.00 sec)

TIMESTAMPADD(unit,interval,datetime expr) --- 为date或者datetime类型的expr增加interval，单位由 unit确定；

　　　　unit单位：MICROSECOND、SECOND、MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

mysql> select timestampadd(minute,1,'2015-4-8');+-----------------------------------+| timestampadd(minute,1,'2015-4-8') |+-----------------------------------+| 2015-04-08 00:01:00               |+-----------------------------------+1 row in set (0.00 sec)mysql> select timestampadd(week,1,'2015-4-8');+---------------------------------+| timestampadd(week,1,'2015-4-8') |+---------------------------------+| 2015-04-15                      |+---------------------------------+1 row in set (0.00 sec)

TIMESTAMPDIFF(unit,datetime expr1,datetime expr2) --返回expr1 - expr2的值，返回值的单位由 unit参数决定。

mysql> select timestampdiff(MONTH,'2015-4-8','2015-1-1');+--------------------------------------------+| timestampdiff(MONTH,'2015-4-8','2015-1-1') |+--------------------------------------------+|                                         -3 |+--------------------------------------------+1 row in set (0.00 sec)mysql> select timestampdiff(MINUTE,'2015-1-1','2015-4-8');+---------------------------------------------+| timestampdiff(MINUTE,'2015-1-1','2015-4-8') |+---------------------------------------------+|                                      139680 |+---------------------------------------------+1 row in set (0.00 sec)

TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() -- 返回unix时间戳

mysql> select unix_timestamp();+------------------+| unix_timestamp() |+------------------+|       1428042298 |+------------------+1 row in set (0.00 sec)

UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (0-53)
YEAR() --- 获取年份

mysql> select year(now());+-------------+| year(now()) |+-------------+|        2015 |+-------------+1 row in set (0.00 sec)

YEARWEEK() Return the year and week

爱程序网

Tags：mysql 日期时间处理函数