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 unit
date- 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.00sec)
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 1second;+-------------------------------------------------------+
| '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> selectcurdate();+------------+
| curdate() |
+------------+
| 2015-04-03 |
+------------+
1 row in set (0.00sec)
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> selectcurtime();+-----------+
| curtime() |
+-----------+
| 14:06:30 |
+-----------+
1 row in set (0.00sec)
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> selectdate(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> selectHOUR(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.00sec)
mysql> select last_day('2004-2-5');+----------------------+
| last_day('2004-2-5') |
+----------------------+
| 2004-02-29 |
+----------------------+
1 row in set (0.00sec)
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> selectmonthname(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.00sec)
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, orYEAR.
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.00sec)
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.00sec)
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> selectunix_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