Date and Time Functions

Date and Time Functions  12.7

Name	Description
​​​ADDDATE()​​​	Add time values (intervals) to a date value
​​​ADDTIME()​​​	Add time
​​​CONVERT_TZ()​​​	Convert from one time zone 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()​​​	Extract the date part of a date or datetime expression
​​​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
​​​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()​​​	Create time from hour, minute, second
​​​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()​​​	Synonym for DATE_SUB() when invoked with three arguments
​​​SUBTIME()​​​	Subtract times
​​​SYSDATE()​​​	Return the time at which the function executes
​​​TIME()​​​	Extract the time portion of the expression passed
​​​TIME_FORMAT()​​​	Format as time
​​​TIME_TO_SEC()​​​	Return the argument converted to seconds
​​​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 (1-53)
​​​YEAR()​​​	Return the year
​​​YEARWEEK()​​​	Return the year and week

NOW()

现在的日期+时间

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP

CURDATE()

select CURDATE() 当天

CURRENT_DATE(), CURRENT_DATE

SELECT CURDATE() + 0  返回 20201118

CURTIME()

当前时间

CURRENT_TIME(), CURRENT_TIME

UTC_DATE()

当前的utc日期

UTC_TIME()

当前的utc时间

UTC_TIMESTAMP()

当前的utc日期+utc时间

DAYOFYEAR

​1年中的第几天​

Return the day of the year (1-366)

​select DAYOFYEAR(CURDATE())​

DAYOFMONTH()

​几号​

SELECT DAYOFMONTH('2020-11-18')

Return the day of the month (0-31)

DAYOFWEEK

本周第几天，星期天是1，星期六是7   ​注意这个和WEEKDAY区别​

WEEKDAY(date)

(​​0​​​ = Monday, ​​1​​​ = Tuesday, … ​​6​​ = Sunday)   ​注意这个和DAYOFWEEK区别​

select DAYOFWEEK('2020-11-22')

MONTH()

​几月 ​

11月是11

SELECT MONTH('2020-11-18')

DATE

SELECT DATE('2003-12-31 01:02:03')   返回 2003-12-31

提取日期

HOUR

SELECT HOUR(now())

现在是几点

DATE_SUB

Subtract a time value (interval) from a date 从日期中减去时间值（间隔）

同义词 SUBDATE()

# 10天前
select DATE_SUB(CURDATE(),INTERVAL 10 DAY)

# 10年前
select DATE_SUB(CURDATE(),INTERVAL 10 YEAR)

DATE_ADD()

Add time values (intervals) to a date value 

同义词 ADDDATE()

将时间值（间隔）添加到日期

# 1天后
SELECT DATE_ADD(now(),INTERVAL 1 DAY)

# 1月后
SELECT DATE_ADD(now(),INTERVAL 1 MONTH)

# 1年后
SELECT DATE_ADD(now(),INTERVAL 1 YEAR)

DATEDIFF

returns expr1 − expr2  仅计算日期

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30')

LAST_DAY

SELECT LAST_DAY(now())

本月最后1天

STR_TO_DATE

SELECT STR_TO_DATE('11/18/2020', '%m/%d/%Y')

SLEEP

SYSDATE（）返回执行时间。 这与NOW（）的行为不同，后者的行为返回一个恒定时间，该时间指示语句开始执行的时间。