MySQL commonly used time functions detailed of recommendations
- 2021-01-19 22:30:38
- OfStack
2.6 DATE_SUB/DATE_ADD
DATE_SUB(date,INTERVAL expr type)
The date argument is a valid date expression. The expr parameter is the interval you want to add.
SELECT id FROM my_table WHERE create_time >= date_sub(now(), INTERVAL 3 HOUR) AND create_time < now();
Type value
•MICROSECOND
•SECOND
•MINUTE
•HOUR
•DAY
•WEEK
•MONTH
•QUARTER
•YEAR
•SECOND_MICROSECOND
•MINUTE_MICROSECOND
•MINUTE_SECOND
•HOUR_MICROSECOND
•HOUR_SECOND
•HOUR_MINUTE
•DAY_MICROSECOND
•DAY_SECOND
•DAY_MINUTE
•DAY_HOUR
•YEAR_MONTH
2.7 Time addition and subtraction
When we give now () +-1 time, we should understand it like this:
+1/+01: Add 1 second
+101/+0101: Add 1 minute and 1 second
+10101/+010101: Plus 1 hour, 1 minute, 1 second
+1010101/+01010101: Plus 1 hour, 1 minute, 1 second per day
+101010101/+0101010101: Plus January 1 day 1:00, 1 minute, 1 second
+1101010101/+010101010101: plus 1 January 1, 1 day, 1 hour, 1 minute, 1 second. Note here that this part of year can be 4 digits (if there is no high position, zero will be added) : 00010101010101
2.8 DATE_DIFF/TIME_DIFF
DATEDIFF(date1,date2)
The DATEDIFF() function returns the number of days between two dates (days only). The date1 and date2 arguments are valid datetime/date expressions.
select datediff('2016-03-29','2016-03-29');
select datediff('2016-03-29 00:00:00','2016-03-29 23:59:59');
TIMEDIFF(date1,date2)
The TIMEDIFF() function returns the number of minutes and seconds between two dates (HH:MM:ss). The date1 and date2 arguments are datetime/time expressions.
select timediff('2016-03-30 00:00:00','2016-03-28 11:11:11');
select timediff('00:00:00','11:11:11');
2.9 DATE_FORMAT
DATE_FORMAT(date,format)
The DATE_FORMAT() function is used to display date/time data in different formats.
SELECT DATE_FORMAT(insert_time,'%Y-%m-%d %H:%i:%S') AS insert_time FROM user;
SELECT DATE_FORMAT(insert_time,'%Y-%m-%d') AS day, COUNT(id) AS count FROM user GROUP BY day;
格式 | 描述 |
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微秒 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是1周的第1天 |
%u | 周 (00-53) 星期1是1周的第1天 |
%V | 周 (01-53) 星期日是1周的第1天,与 %X 使用 |
%v | 周 (01-53) 星期1是1周的第1天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期6) |
%X | 年,其中的星期日是周的第1天,4 位,与 %V 使用 |
%x | 年,其中的星期1是周的第1天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |