Simple! Just one step to help you use mysql to implement date and time query

mysql Query the data within a specified date based on the timestamp Not everyone knows how to query the time period in MySql. Here is a method of querying the time period in MySql for your reference. I hope it can inspire you.

select now();--当前日期及时间 2018-07-25 14:29:36
select LAST_DAY('2018-02-01'); --返回月份中的最后一天  2018-02-28
select DATE_FORMAT(now(),'%Y-%m-%d %H:%m:%S')  --格式化输出 2018-07-25 14:07:18
select '2018-07-25' =(select DATE_FORMAT(now(),'%Y-%m-%d')); --判断当前日期是否与指定日期相等，返回0表示不相等，1表示相等 1
set @dt= (SELECT DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s')); --格式化当前日期和时间，并赋值给@dt 
SELECT @dt;--2018-07-25 14:33:50
SELECT DATE(@dt);--2018-07-25
SELECT TIME(@dt); --14:33:50
SELECT DATE(NOW()); --只输出当前日期 2018-07-25
SELECT TIME(NOW()); --只输出当前时间 14:36:19
SELECT YEAR(now()); --2018
SELECT month(now()); --7
select day(now()); --25
SELECT time(now()) time1 , sleep(3) ,time(now()) time2; --中间睡眠3s钟 time1和time2的时间也是一样的 14:43:54	0	14:43:54
SELECT time(SYSDATE()) time1,sleep(3),time(SYSDATE()) time2; --中间睡眠3s钟 time1和time2的时间相差3s  14:43:15	0	14:43:18

--其他获取当前日期和时间的函数
select CURRENT_DATE,CURRENT_TIME,CURRENT_USER,LOCALTIME,LOCALTIMESTAMP,date(LOCALTIME),date(LOCALTIMESTAMP),time(LOCALTIME);--2018-07-25	14:48:30	root@localhost	2018-07-25 14:48:30	2018-07-25 14:48:30	2018-07-25	2018-07-25	14:48:30
set @dt='2018-02-14 14:22:23';
SELECT DAYOFMONTH('2018-02-14') --一月的第几天 14
SELECT DAYOFWEEK('2018-02-14') --一周的第几天 注：从周日算起 4
SELECT DAYOFYEAR(@dt) --一年的第几天 45
select WEEKOFYEAR(@dt) --一年的第几周 7

Format the date value according to the format string:

• %S, %s seconds in two-digit format (00,01, ..., 59)

• %I, %i two-digit minute (00,01, ..., 59)

• %H two-digit number Numeric hour, 24 hours (00,01, ..., 23)

• %h Two-digit hour, 12 hours (01,02, ..., 12)

• %k hour in numeric form, 24 hours (0,1, ..., 23)

• %l numeric form hour, 12 hours (1, 2, ..., 12)

• %T 24-hour time format (hh:mm:ss)

• %r 12-hour time format (hh:mm:ss AM or hh:mm:ss PM)

• %p AM or PM

• %W The name of each day of the week (Sunday, Monday, ..., Saturday)

• %a The abbreviation of the name of each day of the week (Sun, Mon, ..., Sat)

• %d Two digits represent the number of days in the month (00, 01,..., 31)

• %e The number form represents the number of days in the month (1, 2, ..., 31)

• %D The English suffix represents the number of days in the month (1st, 2nd, 3rd,. ..)

• %w represents the day of the week in numeric form (0 = Sunday, 1=Monday, ..., 6=Saturday)

• %j represents the number of days in the year (001, 002, ..., 366) with three digits

• %U week (0, 1, 52), where Sunday is the first day of the week

• %u week (0, 1, 52), where Monday is the first day of the week

• %M Month name (January, February, ..., December)

• %b Abbreviated month name (January, February, ...., December)

• %m The month represented by two digits (01, 02, ..., 12)

• %c The month represented by digits (1, 2 , ...., 12)

• %Y The year represented by four digits

• %y The year represented by two digits

• %% direct value "%"

Related articles:

mysql query timestamp and date conversion

MySQL time and date query methods and functions

Related videos:

PHP makes a calendar plug-in for lunisolar calendar conversion

The above is the detailed content of Simple! Just one step to help you use mysql to implement date and time query. For more information, please follow other related articles on the PHP Chinese website!