How to query MySQL date and time fields

1. Overview of date and time types

The date and time types supported by MySQL include DATETIME, TIMESTAMP, DATE, TIME, and YEAR.

A comparison of several types is as follows:

When it comes to date and time field type selection, just select the appropriate type according to your storage requirements.

2. Date and time related functions

There are many functions for processing date and time fields, some of which are often used in queries.

Introduced below How to use the following related functions:

• CURDATE and CURRENT_DATE have the same function and return the date value of the current system.

• The two functions CURTIME and CURRENT_TIME have the same effect and return the current system time value.

• NOW() and SYSDATE() functions have the same effect and return the date and time values ​​of the current system.

• UNIX_TIMESTAMP Gets the UNIX timestamp function and returns an unsigned integer based on the UNIX timestamp.

• FROM_UNIXTIME Converts UNIX timestamp to time format, and is the inverse function of UNIX_TIMESTAMP.

• TO_DAYS() Extracts a date value and returns the number of days since AD ​​0.

• DAY() Gets the day value in the specified date or time.

• DATE() Gets the date in the specified date or time.

• TIME() Gets the time in the specified date or time.

• MONTH Gets the month in the specified date.

• WEEK Gets the week number of the year for the specified date.

• YEAR Gets the year.

• QUARTER Gets the quarter value of the date.

• DATE_ADD and ADDDATE functions have the same function, they both add the specified time interval to the date.

• DATE_SUB and SUBDATE functions have the same function, they both subtract the specified time interval from the date.

• ADDTIME Time addition operation adds the specified time to the original time.

• SUBTIME time subtraction operation, subtracts the specified time from the original time.

• DATEDIFF Gets the interval between two dates and returns the value of parameter 1 minus parameter 2.

• DATE_FORMAT formats the specified date and returns the value in the specified format according to the parameters.

Some usage examples:

mysql> select CURRENT_DATE,CURRENT_TIME,NOW();
+--------------+--------------+---------------------+
| CURRENT_DATE | CURRENT_TIME | NOW()               |
+--------------+--------------+---------------------+
| 2020-06-03   | 15:09:37     | 2020-06-03 15:09:37 |
+--------------+--------------+---------------------+

mysql> select TO_DAYS('2020-06-03 15:09:37'),
TO_DAYS('2020-06-03')-TO_DAYS('2020-06-01');
+--------------------------------+---------------------------------------------+
| TO_DAYS('2020-06-03 15:09:37') | TO_DAYS('2020-06-03')-TO_DAYS('2020-06-01') |
+--------------------------------+---------------------------------------------+
|                         737944 |                                           2 |
+--------------------------------+---------------------------------------------+

mysql> select MONTH('2020-06-03'),WEEK('2020-06-03'),YEAR('2020-06-03');
+---------------------+--------------------+--------------------+
| MONTH('2020-06-03') | WEEK('2020-06-03') | YEAR('2020-06-03') |
+---------------------+--------------------+--------------------+
|                   6 |                 22 |               2020 |
+---------------------+--------------------+--------------------+

# DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数
mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
    -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
|    1 |  -15 |
+------+------+

3. Standard query for date and time fields

To meet our query needs Be prepared, project requirements often involve conditional filter queries based on date or time. Let's explore how to query and filter date and time fields, because such needs are diverse.

First of all, in order to make the query more accurate, the data must be inserted according to the specifications. To ensure that the year uses 4 digits and the date and month are within a reasonable range, we created a table and inserted some data to facilitate testing.

CREATE TABLE `t_date` (
`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`year_col` YEAR NOT NULL  COMMENT '年',
`date_col` date NOT NULL  COMMENT '日期',
`time_col` time NOT NULL  COMMENT '时间',
`dt_col` datetime NOT NULL  COMMENT 'datetime时间',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='time测试表';
# 日期和时间都选取当前的日期或时间
INSERT INTO t_date (year_col,date_col,time_col,dt_col,create_time) VALUES 
(year(now()),DATE(NOW()),time(now()),NOW(),NOW());
# 指定日期或时间插入
INSERT INTO t_date ( `year_col`, `date_col`, `time_col`, `dt_col`, `create_time` )
VALUES
    ( 2020, '2020-06-03', '09:00:00', '2020-06-03 10:04:04', '2020-06-03 10:04:04' ),
    ( 2020, '2020-05-10', '18:00:00', '2020-05-10 16:00:00', '2020-05-10 16:00:00' ),
    ( 2019, '2019-10-03', '16:04:04', '2019-10-03 16:00:00', '2019-10-03 16:00:00' ),
    ( 2018, '2018-06-03', '16:04:04', '2018-06-03 16:00:00', '2018-06-03 16:00:00' ),
    ( 2000, '2000-06-03', '16:04:04', '2000-06-03 08:00:00', '2000-06-03 08:00:00' ),
    ( 2008, '2008-06-03', '16:04:04', '2008-06-03 08:00:00', '2008-06-03 08:00:00' ),
    ( 1980, '1980-06-03', '16:04:04', '1980-06-03 08:00:00', '1980-06-03 08:00:00' );

Based on the data in the above test table, let’s learn how to write several common query statements:

Query based on date or time equivalent:

select * from t_date where year_col = 2020;
select * from t_date where date_col = '2020-06-03';
select * from t_date where dt_col = '2020-06-03 16:04:04';

Query based on date or time range:

select * from t_date where date_col > '2018-01-01';
select * from t_date where dt_col >= &#39;2020-05-01 00:00:00&#39; and dt_col < &#39;2020-05-31 23:59:59&#39;;
select * from t_date where dt_col between &#39;2020-05-01 00:00:00&#39; and  &#39;2020-05-31 23:59:59&#39;;

Query this month’s data:

# 查询create_time在本月的数据
select * from t_date where DATE_FORMAT(create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' );

Query the most recent days Data:

# 以date_col为条件 查询最近7天或30天的数据
SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(date_col);
SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(date_col);

Other types of query writing:

# 查询今天的数据
SELECT * FROM t_date WHERE TO_DAYS(create_time) = TO_DAYS(NOW());
# 查询某个月的数据
SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y-%m')='2020-06';
# 查询某年的数据
SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y')= 2020;
SELECT * FROM t_date WHERE YEAR(create_time) = 2020;
# 根据日期区间查询数据,并排序
SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y') BETWEEN '2018' AND '2020' ORDER BY create_time DESC;

The above is the detailed content of How to query MySQL date and time fields. For more information, please follow other related articles on the PHP Chinese website!