Các hàm xử lý về thời gian trong mySQL - Date and Time Functions

Tên hàm Mô tả
ADDDATE() = Add time values (intervals) to a date value
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 time values (intervals) to a date value
DATE_FORMAT() = Format date as specified
DATE_SUB() = Subtract a time value (interval) from a date
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 (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() = Format UNIX timestamp as a date
GET_FORMAT() = Return a date format string
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() = 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() = 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 (0-53)
YEAR() = Return the year
YEARWEEK() = Return the year and week

Ví dụ Minh họa
mysql> SELECT DAYOFMONTH('2013-11-00'), MONTH('2011-00-00');
-> 0, 0

mysql> SELECT DATE_ADD('2013-05-00',INTERVAL 1 DAY);
-> NULL
mysql> SELECT DAYNAME('2013-05-00');
-> NULL

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'

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'

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

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

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

mysql> SELECT DATEDIFF('2011-12-31 23:59:59','2011-12-30');
-> 1
mysql> SELECT DATEDIFF('2012-11-30 23:59:59','2012-12-31');
-> -31