mysql时间日期处理_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 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