What are the commonly used date and time functions in MySQL?

What are the commonly used date and time functions in MySQL? This article will introduce it to you, as shown in the figure below:

CURDATE(), CURRENT_DATE() returns the current date 'YYYY-MM-DD'

CURTIME(), CURRENT_TIME() returns the current time 'HH:MM:SS'

CURRENT_TIMESTAMP()

LOCALTIME()

NOW()

SYSDATE() returns the current date and time 'YYYY-MM-DD HH:MM:SS'

UNIX_TIMESTAMP( date) returns the number of seconds after Unix timestamp 1970-01-01 00:00:00. date can be a specified time, such as DATE string, DATETIME string, TIMESTAMP string, etc.

FROM_UNIXTIME(date) Converts unix timestamp to common format, which is the inverse function of UNIX_TIMESTAMP(date)

UTC_DATE() Returns the current UTC (Universal Standard Time) date value

UTC_TIME() returns the current UTC time value

--8 hours difference

MONTH(date ) Returns the month of date 1-12

MONTHNAME(date) Returns the full English name of the month of date

DAYNAME(date) Returns the day of the week corresponding to date The English name of a certain day

##DAYOFWEEK(date) returns the index position of date in the week, 1--Sunday...7-Saturday

WEEKDAY(date) returns the index position of date in the week 0-Monday 6-Sunday

--The above 2017-07-03 is Monday

WEEK(date) returns the week number of the year in date. It can have a second parameter to determine whether it starts on Monday or Sunday and the return value is 0~53 or 1~53

WEEKOFYEAR(date) Returns the week number of date in the year

DAYOFYEAR(date) Returns the date of date in the year The number of days in range 1~366

DAYOFMONTH(date) Returns the day of the month when date is returned 1~31

YEAR(date ) Return the year of the date --- 00-69 Returns 2000~2069 70-99 Returns 1970~1999

QUARTER(date) Returns the quarter Returns 1~4

MINUTE(time) Returns the number of minutes of time

SECOND(time) Returns the number of seconds of time

EXTRACT(type FROM date) Returns the type value extracted from date

##TIME_TO_SEC(time) Returns the number of seconds to convert time

SEC_TO_TIME(sec) Returns the result of converting seconds to hours, minutes and seconds

DATE_ADD(date,INTERVAL expr type)

ADDDATE(date,INTERVAL expr type) Add operation to date

##DATE_SUB(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type) Subtract the date

##ADDTIME(date,expr) date-is a date or time expression, expr-is a time expression, return date plus expr time The result

SUBTIME(date,expr) returns the time of date minus expr

##DATEDIFF(date1, date2 ) Returns the day value of date1-date2

DATE_FORMAT(date,format) Returns the date value in the format specified by format

##TIME_FORMAT(time,format) Returns the format format The time value

GET_FORMAT(val_type,format_type)

val_type--DATE, DATETIEM, TIME

format_type--EUR, INTERVAL , ISO, JIS, USA

Return the corresponding format format

The above is the detailed content of What are the commonly used date and time functions in MySQL?. For more information, please follow other related articles on the PHP Chinese website!