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()的行为不同,后者的行为返回一个恒定时间,该时间指示语句开始执行的时间。 

Date and Time Functions_执行时间