mysql函数之日期时间
2013-07-22 16:27:23 
阿炯
Mysql中有许多常用的日期时间函数，下表是目前系统所有的相关函数。名称 描述
ADDDATE() Add dates
ADDTIME() Add time
CONVERT_TZ() Convert from one timezone to another
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE_ADD() Add two dates
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract two dates
DATE() Extract the date part of a date or datetime expression
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (1-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() Format date as a UNIX timestamp
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME MAKETIME()
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
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() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() When invoked with three arguments a synonym for DATE_SUB()
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
UNIX_TIMESTAMP() Return a UNIX timestamp
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 (1-53)
YEAR() Return the year
YEARWEEK() Return the year and week
关于更多的日期函数使用，请参考：Mysql日期时间函数使用参考
我们重点介绍一些常用的：
curdate()--当前日期
curtime()--当前时间
now()--当前的日期时间
unix_timestamp(date)--返回date的unix时间戳
from_unixtime--返回unix时间戳的日期值
week(date)--返回date为一年的第几周
year(date)--返回date的年份
hour(time)--返回time的小时值
minute(time)--返回time的分钟值
monthname(date)--返回date的月份名
date_format(date,fmt)--返回按字符串fmt格式化后的日期值
datediff(expr,expr2)--返回起始时间expr和结束时间expr2间的天数
date_add(date,interval expr type)--返回一个日期时间加上一个时间间隔的时间值
我们着重介绍后一个函数，interval是间隔类型关键字，expr为一表达式，这个表达式对应后面的类型，type是间隔类型。mysql提供了13种类型，具体如下表：
表达式类型--描述--格式
hour--小时--hh
minute--分钟--mm
second--秒--ss
year--年-YY
month--月--MM
day--天--DD
year_month--年月--YY-MM
day_hour--日及小时--DD hh
day_minute--日及分钟--DD hh:mm
day_second--日及秒--DD hh:mm:ss
hour_minute--小时及分--hh:mm
hour__second--小时及秒--hh:ss
minute_second--分钟及秒--mm:ss
示例如下：返回当前日期时间、离当前31天后的日期时间、离当前一年三个月后的日期时间、离当前31天前的日期、一年又三个月前的日期时间。
mysql> select now(),date_add(now(),interval 31 day) as after31day,date_add(now(),interval '1_3' year_month) as afterone_three_year_month,date_add(now(),interval -31 day) as before31day,date_add(now(),interval '-1_-3' year_month) as beforeone_three_year_month;
+---------------------+---------------------+---------------------------+---------------------+----------------------------+
| now() | after31day | afterone_three_year_month | before31day | beforeone_three_year_month |
+---------------------+---------------------+---------------------------+---------------------+----------------------------+
| 2013-07-22 16:20:16 | 2013-08-22 16:20:16 | 2014-10-22 16:20:16 | 2013-06-21 16:20:16 | 2012-04-22 16:20:16 |
+---------------------+---------------------+---------------------------+---------------------+----------------------------+