当前位置：网站首页>[MySQL] 15 super detailed MySQL date type function summary

[MySQL] 15 super detailed MySQL date type function summary

2022-07-21 21:17:00 【Zimujun】

function	effect
CURDATE(), CURRENT_DATE()	Return current date , Include only mm / DD / yyyy
CURTIME()	Return current time , Only hours, minutes, seconds
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	Returns the current system date and time
UTC_DATE()	return UTC( World standard time ) date
UTC_TIME()	return UTC( World standard time ) Time

function	effect
UNIX_TIMESTAMP()	With UNIX Returns the current time in the form of a timestamp .SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	Time date With UNIX Return in the form of a timestamp .
FROM_UNIXTIME(timestamp)	take UNIX Time stamp converted to normal format

function	effect
YEAR(date) / MONTH(date) / DAY(date)	Return the specific date value
HOUR(time) / MINUTE(time) / SECOND(time)	Return the specific time value
MONTHNAME(date)	Return to the month ：January
DAYNAME(date)	Day of the week ：MONDAY
WEEKDAY	What day of the week ： Monday is 0, It was on Tuesday 1,…, Sunday is 6
QUARTER(date)	Returns the quarter corresponding to the date , Range 1~4
WEEK(date), WEEKOFYEAR(date)	Go back to the week of the year
DAYOFYEAR(date)	The return date is the day of the year
DAYOFMONTH(date)	The return date is the day of the month
DAYOFWEEK(date)	What day of the week , Be careful ： Sunday is 1,…, Saturday is 7

function	effect
EXTRACT(typr FROM date)	Returns a specific part of a specified date ,type Specifies the value to be returned

EXTRACT(typr FROM date) Function type The value and meaning of ：

function	effect
TIME_TO_SEC(time)	take time Convert to seconds and return the result value . Change the formula to ： `Hours ×3600+ minute ×60+ second`
SEC_TO_TIME(seconds)	take seconds Convert to time including hours, minutes and seconds

function	effect
DATE_ADD(datatime, INTERVAL expr type), ADDDATE(date, INTERVAL expr type)	Returns the difference between the given date and time INTERBAL Date time of the time period
DATE_SUB(date, INTERVAL expr type), SUBDATE(date, INTERVAL expr type)	Return and date Difference between INTERVAL The date of the interval

In the above functions type The value of ：

The first 2 Group ：

function	effect
ADDTIME(time1, time2)	return time1 add time2 Time for . When time2 When it is a number , It stands for seconds , Can be negative
SUBTIME(time1, time2)	return time1 subtract time2 After time . When time2 When it is a number , It stands for seconds , Can be negative
DATEDIFF(date1, date2)	return date1-date2 Number of days between dates
TIMEDIFF(time1, time2)	return time1-time2 Time interval of
FROM_DAYS(N)	Return from 0000 year 1 month 1 The date of ,N Days later
TO_DAYS(date)	Return date date distance 0000 year 1 month 1 Days of the day
LAST_DAY(date)	return date The date of the last day of the month
MAKEDATE(year, n)	Returns a date for a given year and the number of days in the year
MAKETIME(hour, minute, second)	Combine the given hours, minutes and seconds into time and return
PERIOD_ADD(time, n)	return time add n After time

function	effect
DATE_FORMAT(date, fmt)	According to the string fmt Format date date value
TIME_FORMAT(time, fmt)	According to the string fmt Format time time value
GET_FORMAT(date_type, format_type)	Returns the display format of the date string
STR_TO_DATE(str, fmt)	According to the string fmt Yes str To analyze , Resolve to a date

Above Not GET_FORMAT Function fmt Common format characters for parameters ：

Format symbol	explain	Format symbol	explain
%Y	4 The number of digits indicates the year	%y	2 The number of digits indicates the year
%M	The name of the month means the month (January,…)	%m	2 Digits indicate months (01, …)
%b	Abbreviated month name (Jan., Feb., …)	%c	The number represents the month (1, 2, …)
%D	The English suffix indicates the number of days in the month (1st, 2nd, …)	%d	2 Digits indicate the number of days in the month (01, 02, …)
%e	The number of days in the month (1, 2, …)
%H	2 Digits indicate hours ,24 hourly (01, …, 24)	%h or %l	2 Digits indicate hours ,12 hourly
%k	The number represents the hour ,24 hourly (1, 2, …)	%l	The number represents the hour ,12 hourly (1, 2, …)
%i	2 Digits indicate minutes (01, …)	%S or %s	2 Digits indicate seconds (01, …, 60)
%W	The name of the week of the week (Sunday, …)	%a	Abbreviation of week in a week (Sun,…, Mon.)
%w	The number of days in the week (0=Sunday, 1=Monday, …)
%j	3 The number of digits represents the number of days in the year (001, …365)	%U	The numerical form represents the week of the year (1, 2, …) among Sunday For the first day of the week
%u	The numerical form represents the week of the year (1, 2, …) among Monday For the first day of the week
%T	24 hourly	%r	12 hourly
%p	AM or PM	%%	Express `%`

GET_FORMAT Function date_type and format_type The parameter values are as follows ：