catalogue
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
Value and meaning of type in EXTRACT(type FROM date) function:
Time and second conversion function
Function to calculate date and time
The value of type in the above functions:
Formatting and parsing of date
Above non GET_FORMAT characters commonly used for fmt parameters in format function:
Parsing: inverse of formatting
GET_ Date in format function_ Type and format_ The value of the type parameter is as follows:
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'));