mysql可以处理日期和时间的函数（mysql日期与时间函数）

mysql可以处理日期和时间的函数（mysql日期与时间函数）

• ADDDATE()
• ADDTIME()
• CONVERT_TZ()
• CURDATE()
• CURRENT_DATE() CURRENT_DATE
• CURRENT_TIME() CURRENT_TIME
• CURRENT_TIMESTAMP() CURRENT_TIMESTAMP
• CURTIME()
• DATE()
• DATE_ADD()
• DATE_FORMAT()
• DATE_SUB()
• DATEDIFF()
• DAY()
• DAYNAME()
• DAYOFMONTH()
• DAYOFWEEK()
• DAYOFYEAR()
• EXTRACT()
• FROM_DAYS()
• FROM_UNIXTIME()
• GET_FORMAT()
• HOUR()
• LAST_DAY
• LOCALTIME() LOCALTIME
• LOCALTIMESTAMP LOCALTIMESTAMP()
• MAKEDATE()
• MAKETIME()
• MICROSECOND()
• MINUTE()
• MONTH()
• MONTHNAME()
• NOW()
• PERIOD_ADD()
• PERIOD_DIFF()
• QUARTER()
• SEC_TO_TIME()
• SECOND()
• STR_TO_DATE()
• SUBDATE()
• SUBTIME()
• SYSDATE()
• TIME()
• TIME_FORMAT()
• TIME_TO_SEC()
• TIMEDIFF()
• TIMESTAMP()
• TIMESTAMPADD()
• TIMESTAMPDIFF()
• TO_DAYS()
• TO_SECONDS()
• UNIX_TIMESTAMP()
• UTC_DATE()
• UTC_TIME()
• UTC_TIMESTAMP()
• WEEK()
• WEEKDAY()
• WEEKOFYEAR()
• YEAR()
• YEARWEEK()

ADDDATE(date INTERVAL expr unit) ADDDATE(expr days)

mysql> SELECT DATE_ADD('2008-01-02' INTERVAL 31 DAY); -> '2008-02-02' mysql> SELECT ADDDATE('2008-01-02' INTERVAL 31 DAY); -> '2008-02-02'

CONVERT_TZ(dt from_tz to_tz)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00' 'GMT' 'MET'); -> '2004-01-01 13:00:00' mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00' ' 00:00' ' 10:00'); -> '2004-01-01 22:00:00'

CURDATE()

mysql> SELECT CURDATE(); -> '2008-06-13' mysql> SELECT CURDATE() 0; -> 20080613

mysql> SELECT CURTIME(); -> '23:50:26' mysql> SELECT CURTIME() 0; -> 235026.000000

DATE_FORMAT(date format)

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00' '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00' '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00' -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00' -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01' '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00' '%d'); -> '00'

mysql> SELECT FROM_UNIXTIME(1447430881); -> '2015-11-13 10:08:01' mysql> SELECT FROM_UNIXTIME(1447430881) 0; -> 20151113100801 mysql> SELECT FROM_UNIXTIME(1447430881 -> '%Y %D %M %h:%i:%s %x'); -> '2015 13th November 10:08:01 2015'