Detailed explanation of the usage of date function in MySQL learning

This article brings you relevant knowledge about mysql, which mainly introduces MySQL’s date functions, including obtaining system time functions, date formatting functions, etc. Let’s learn about it together Take a look, hope it helps everyone.

Recommended learning: mysql video tutorial

Get the system time function

"NOW()" function can Get the current system date and time in the following format: "YYYY-MM-DD hh:mm:ss" (the hour unit here is 24-hour system)

The "CURDATE()" function can get the current system date and time Date, the format is as follows: "YYYY-MM-DD"

"CURTIME()" function can get the current system time, the format is as follows: "hh:mm:ss" (24-hour format)

SELECT NOW();            -- 返回结果 "2020-06-10 17:22:51"  (示例)

SELECT CURDATE();        -- 返回结果 "2020-06-10"

SELECT CURTIME();        -- 返回结果 "17:22:52"

Date formatting function

The "DATE_FORMAT()" function is used to format dates and can help us extract very useful date information

The syntax is as follows:

DATE_FORMAT(date, expression)

SELECT ename, DATE_FORMAT(hiredate,"%Y") AS "入职日期" FROM t_emp;

##PlaceholderFunctionPlaceholderFunction%YYear%mMonth%dDate%wWeek (number) - (0 is Sunday)%WWeek (name) - (English)%jDay of the year%UThe number of weeks in this year%Hhours(24)%hHours (12)%iMinutes%sSeconds%rTime(24)%T Time(12)Date function practice

Use the "date function" to query the day of the week when your birthday is.

SELECT DATE_FORMAT("2018-01-01","%W");        -- 返回结果为 "Monday"

Date function exercise

Use "date function" to query how many employees joined the company in the first half of 1981?

SELECT COUNT(*)
FROM t_emp
WHERE DATE_FORMAT(hiredate,"%Y") = 1981
AND DATE_FORMAT(hiredate,"%m") <= 6;

Notes on date calculation

In MySQL, two dates cannot be added or subtracted directly; at the same time, Dates cannot be added or subtracted from numbers.

The reason is that date is a special calculation unit, and the bases are not like ordinary decimals.

Although we use date to perform the operation of "1" without causing a syntax error, the result obtained is a pure number, not the result in the date format we want. (Examples are as follows)

SELECT ename, hiredate, hiredate+1 FROM t_emp;

Date offset calculation

DATE_ADD() function can implement date offset calculation, and in the processing of time units, Relatively flexible.

The syntax is as follows:

SELECT DATE_ADD("原始日期", INTERVAL, 偏移量, 时间单位)        -- INTERVAL 是 关键字

"DATE_ADD() function" The demonstration case is as follows

SELECT DATE_ADD(NOW(), INTERVAL 10 DAY);         -- 得到的结果为 10 天 之后的日期时间

SELECT DATE_ADD(NOW(), INTERVAL -500 MINUTE);         -- 得到的结果为 500 分钟 之前的日期时间

SELECT DATE_ADD(DATE_ADD(NOW(), INTERVAL -6 MONTH), INTERVAL -3 DAY);         -- 得到的结果为 6 个月 3 天 之前的日期时间

SELECT 
DATE_FORMAT(
DATE_ADD(DATE_ADD(NOW(), INTERVAL -6 MONTH), INTERVAL -3 DAY) , "%Y-%m-%d") AS DATE;     -- 返回 "%Y-%m-%d" 格式结果

Calculate the number of days between dates

DATEDIFF() function is used To calculate the number of days between two dates, the syntax is as follows:

DATEDIFF("date", "date")

Query 10 departments with annual income exceeding 15,000 Information about employees with more than 20 years of service.

SELECT empno, ename, sal, hiredate
FROM t_emp
WHERE deptno = 10 
AND (sal + IFNULL(comm,0)) * 12 >=15000
AND DATEDIFF(NOW(),hiredate)/365 >= 20

-- IFNULL(expr1,expr2)：IFNULL 函数的语法，当第一个参数的值为null 的时候，则返回第二个参数的值
-- DATEDIFF(expr1,expr2)：DATEDIFF 函数的语法，计算第一个日期与第二个日期的偏差时间差
-- NOW()：NOW 函数可以获得当前日期

Recommended learning:

mysql video tutorial

The above is the detailed content of Detailed explanation of the usage of date function in MySQL learning. For more information, please follow other related articles on the PHP Chinese website!