MySQL common time functions (recommended) and mysql Functions

MySQL common time functions (recommended) and mysql Functions

2.6 DATE_SUB/DATE_ADD

DATE_SUB (date, INTERVAL expr type)

The date parameter is a valid date expression. The expr parameter is the time 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 are giving now () +-a time, we should understand it as follows:

+ 1/+ 01: 1 second
+ 101/+ 0101: 1 minute plus 1 second
+ 10101/+ 010101: 1 hour, 1 minute, 1 second
+ 1010101/+ 01010101: plus 1 day 01:01 minutes 1 second
+ 101010101/+ 0101010101: Add 01:01 on January 1, January 1 minutes and 1 second
+ 1101010101/+ 010101010101: add one year, January 1, January 1, 01:01, and 1 second. Note that this part of the year can be four (if it is not high, it will be zero): 00010101010101

2.8 DATE_DIFF/TIME_DIFF

DATEDIFF (date1, date2)

The DATEDIFF () function returns the number of days (only days) between two dates. The date1 and date2 parameters 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 hours, minutes, And seconds (HH: MM: ss) between two dates. The date1 and date2 parameters 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;

Format	Description
%	Abbreviated name of a week
% B	Abbreviated month name
% C	Month, Value
% D	Day of the month with an English prefix
% D	Day of the month, numeric value (00-31)
% E	Day of the month, value (0-31)
% F	Microseconds
% H	Hour (00-23)
% H	Hour (01-12)
% I	Hour (01-12)
% I	Minute, value (00-59)
% J	Day of the year (001-366)
% K	Hour (0-23)
% L	Hour (1-12)
% M	Month name
% M	Month, value (00-12)
% P	AM or PM
% R	Time, 12-hour (hh: mm: ss AM or PM)
% S	Seconds (00-59)
% S	Seconds (00-59)
% T	Time, 24-hour (hh: mm: ss)
% U	Week (00-53) Sunday is the first day of the week
% U	Week (00-53) Monday is the first day of the week
% V	Week (01-53) Sunday is the first day of the week, used with % X
% V	Week (01-53) Monday is the first day of the week, used with % x
% W	Week name
% W	Day of the week (0 = Sunday, 6 = Saturday)
% X	Year, where Sunday is the first day of the week, four digits, used with % V
% X	Year, where Monday is the first day of the week, 4 digits, used with % v
% Y	Year, 4 digits
% Y	Year, two places

The above is a detailed explanation (recommended) of MySQL common time functions provided by xiaobian. I hope you can provide more support for our customers ~