你的位置:首页 > 数据库

[数据库]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 ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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() Extract the time portion of the expression passed
  • TIMEDIFF() Subtract time
  • TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
  • TIMESTAMPADD() Add an interval to a datetime expression
  • TIMESTAMPDIFF() Subtract an interval from a datetime expression
  • 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