catalogue

Get date and time

Expand: convert a date or time into a numeric value

Date and time stamp conversion

Get functions such as month, week, number of weeks and days

Operation function of date

Value and meaning of type in EXTRACT(type FROM date) function:

Time and second conversion function

Function to calculate date and time

first group

        skill:

                The value of type in the above functions:

Group 2

Formatting and parsing of date

        Above non GET_FORMAT characters commonly used for fmt parameters in format function:

format:

Parsing: inverse of formatting

        GET_ Date in format function_ Type and format_ The value of the type parameter is as follows:

Use formatting type

Get date and time

Functions	usage
CURDATE() ,CURRENT_DATE()	Returns the current date, including only year, month and day
CURTIME() , CURRENT_TIME()	Returns the current time, including only hours, minutes and seconds
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP()	Returns the current system date and time
UTC_DATE()	Returns the UTC date
UTC_TIME()	Returns UTC time

sentence

#Get date and time
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
FROM DUAL;

result

Expand: convert a date or time into a numeric value

# Convert a date or time into a numeric value
SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;

Date and time stamp conversion

Function	usage
UNIX_TIMESTAMP()	Returns the current time as a UNIX timestamp. SELECT UNIX_ TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)	Returns the time date as a UNIX timestamp.
FROM_UNIXTIME(timestamp)	Convert UNIX timestamp time to normal format time

sentence

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-04-14 00:13:57'),FROM_UNIXTIME(1649866390)

result

Get functions such as month, week, number of weeks and days

Function	usage
YEAR(date) / MONTH(date) / DAY(date)	Return specific date value
HOUR(time) / MINUTE(time) /SECOND(time)	Return specific time value
MONTHNAME(date)	Return month: January
DAYNAME(date)	Return the day of the week: Monday, Tuesday SUNDAY
WEEKDAY(date)	Return to the day of the week. Note that week 1 is 0 and week 2 is 1,... Sunday is 6
QUARTER(date)	The quarter corresponding to the return date ranges from 1 to 4
WEEK(date) , WEEKOFYEAR(date)	Returns the week ordinal of the year
DAYOFYEAR(date)	The date of return is the day of the year
DAYOFMONTH(date)	The return date is the day of the month
DAYOFWEEK(date)	What day of the week Note: Sunday is 1 and Monday is 2,... Saturday is 7

sentence

#Get functions such as month, week, number of weeks and days
SELECT YEAR(CURDATE()) "year",MONTH(CURDATE()) "month",DAY(CURDATE()) "day",
HOUR(CURTIME()) "hour",MINUTE(NOW()) "minute",SECOND(SYSDATE()) "second"
FROM DUAL;

result

Statement 2

SELECT MONTHNAME('2022-4-14') "month",DAYNAME('2022-4-14') "What day is today?",WEEKDAY('2022-4-14') "Day of week",
QUARTER(CURDATE()) "Four seasons",WEEK(CURDATE()) "What week",DAYOFYEAR(NOW()) "What day of the year",
DAYOFMONTH(NOW()) "Day of the month",DAYOFWEEK(NOW()) "What day of the week"
FROM DUAL;

result

Operation function of date

Function	usage
EXTRACT(type FROM date)	Returns a specific part of a specified date, and type specifies the returned value

Value and meaning of type in EXTRACT(type FROM date) function:

sentence

SELECT EXTRACT(SECOND FROM NOW()) "Seconds",EXTRACT(DAY FROM NOW()) "Days",
EXTRACT(HOUR_MINUTE FROM NOW()) "hour",EXTRACT(QUARTER FROM '2022-05-12')"quarter"
FROM DUAL;

result

Time and second conversion function

Function	usage
TIME_TO_SEC(time)	Convert time to seconds and return the result value. The conversion formula is: hour * 3600 + minute * 60 + second
SEC_TO_TIME(seconds)	Convert the seconds description to a time that contains hours, minutes, and seconds

sentence

SELECT NOW(),TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(2494)
FROM DUAL;

result

Function to calculate date and time

first group

skill:

Just remember a set of integers plus minus

Function	usage
DATE_ADD(datetime, INTERVAL expr type), ADDDATE(date,INTERVAL expr type)	Returns the date and time of the INTERVAL period different from the given date and time
DATE_SUB(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type)	Returns the date with an INTERVAL different from date

The value of type in the above functions:

Statement 1

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR), # Add one year to the present time
DATE_ADD(NOW(),INTERVAL -1 YEAR),# Subtract one year from the current time
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;

result

Statement 2

SELECT 
DATE_ADD(NOW(), INTERVAL 1 DAY) "Now time plus one day",
DATE_ADD(NOW(),INTERVAL 1 SECOND) AS "Now time plus one second",
ADDDATE(NOW(),INTERVAL 1 SECOND) "Now time plus one second, another way to write",
DATE_ADD(NOW(),INTERVAL '1_1' HOUR_MINUTE) AS "Now time plus an hour and a minute",
DATE_ADD(NOW(), INTERVAL -1 YEAR)  "Subtract one year from the present time", #Can be negative
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) "Now add a year and a month" #Single quotation mark required  # Plus one year and one month
FROM DUAL;

result

Group 2

Function	usage
ADDTIME(time1,time2)	Returns the time of time1 plus time2. When time2 is a number, it represents seconds and can be negative
SUBTIME(time1,time2)	Returns the time after time1 minus time2. When time2 is a number, it represents seconds and can be negative
DATEDIFF(date1,date2)	Returns the number of days between date1 and date2
TIMEDIFF(time1, time2)	Returns the interval between time1 - time2
FROM_DAYS(N)	Returns the date N days after January 1, 0000
TO_DAYS(date)	Return date the number of days from January 1, 0000
LAST_DAY(date)	Returns the date of the last day of the month in which date is located
MAKEDATE(year,n)	Returns a date for a given year and the number of days in that year
MAKETIME(hour,minute,second)	Combines a given hour, minute, and second into a time and returns
PERIOD_ADD(time,n)	Returns the time after time plus n

sentence

SELECT 
ADDTIME(NOW(),20) "+20s",
SUBTIME(NOW(),30) "-30s",
SUBTIME(NOW(),'1:1:3') "-1 Hour 1 minute 3 seconds"
FROM DUAL;

SELECT 
# Difference between two dates   
DATEDIFF(NOW(),'2023-04-14') "Returns the isolation days between the current time and this time",                      
TIMEDIFF(NOW(),'2022-05-14 00:47:54') "Returns the time interval between the current time and this time",
FROM_DAYS(366) "Returns the date after 366 days from January 1, 0000",
TO_DAYS('0000-12-25') "Return date date Days from January 1, 0000";

SELECT
LAST_DAY(NOW()) "Last day of the month", 
MAKEDATE(YEAR(NOW()),32) "Return the current year 2022 and add the value [32] to 2022-02-01",
MAKETIME(10,21,23) "Given combination of hours, minutes and seconds",
PERIOD_ADD(20200101010101,10) # Adding 10 MySQL 5.7 to this time is not easy
FROM DUAL;

result

Formatting and parsing of date

Format: date -- > string
Parsing: String ----- > date

Function	usage
DATE_FORMAT(date,fmt)	Format the date value according to the string fmt
TIME_FORMAT(time,fmt)	Format the time value according to the string fmt
GET_FORMAT(date_type,format_type)	Returns the display format of the date string
STR_TO_DATE(str, fmt)	str is parsed according to the string fmt to a date

Above non GET_FORMAT characters commonly used for fmt parameters in format function:

format:

#format:
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;

Parsing: the inverse process of formatting

#Parsing: the inverse process of formatting
SELECT STR_TO_DATE('2022-April-14th 01:41:42 Thursday 4','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;

GET_ Date in format function_ Type and format_ The value of the type parameter is as follows:

Use formatting type

SELECT GET_FORMAT(DATE,'USA'),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),'%m.%d.%Y'),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'));