Shulou(Shulou.com)05/31 Report--

Most people don't understand the knowledge points of this article "How to query MySQL date and time fields", so Xiaobian summarizes the following contents for everyone. The contents are detailed, the steps are clear, and they have certain reference value. I hope everyone can gain something after reading this article. Let's take a look at this article "How to query MySQL date and time fields".

1. Overview of date and time types

MySQL supports date and time types DATETIME, TIMESTAMP, DATE, TIME, YEAR,

Several types are compared as follows:

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

2. Date and time correlation function

There are many functions that handle date and time fields, some of which are often used in queries,

Here are a few ways to use the following correlation functions:

CURDATE and CURRENT_DATE are two functions that do the same thing and return the date value of the current system.

CURTIME and CURRENT_TIME are two functions that do the same thing and return the current system time value.

NOW() and SYSDATE() are two functions that do the same thing and return the date and time values of the current system.

UNIX_TIMESTAMP gets the UNIX timestamp function, which returns an unsigned integer based on the UNIX timestamp.

FROM_UNIXTIME converts UNIX timestamps to time format, which is the inverse of UNIX_TIMESTAMP.

TO_DAYS() extracts the date value and returns the number of days from AD 0 to the present.

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 of the specified date.

WEEK gets the week of the year the specified date is.

YEAR Get the year.

QUARTER Gets the quarterly value of the date.

DATE_ADD and ADDDATE are the same functions that add a specified time interval to a date.

DATE_SUB and SUBDATE are the same functions, subtracting the specified time interval from the date.

ADDTIME adds the specified time to the original time.

SUBTIME Time Subtraction 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 a value in the specified format depending on the parameters.

Some examples of usage:

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('2017-11-30','2017-12-15') AS col2;+------+------+| COL1 | col2 |+------+------+| 1 | -15 |+-----+-----+3. Canonical queries for date and time fields

The above content is to prepare for our query requirements. In project requirements, we often filter queries based on date or time. Sometimes these requirements vary, so let's learn how to write queries about date and time fields.

First, to make the query more accurate, insert the data according to the specification. For example, the year uses 4 digits, the date and month should be within a reasonable range, etc. Below, for testing convenience, we create a table and insert some data.

CREATE TABLE `t_date`(`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'increment primary',`year_col` YEAR NOT NULL COMMENT ' year',`date_col` date NOT NULL COMMENT 'date',`time_col` time NOT NULL COMMENT' datetime ',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' create time', PRIMARY KEY (`increment_id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time test table';#Date and time both select current date or time INSERT INTO t_date (year_col,date_col,time_col,dt_col,create_time) VALUES (year(now()),DATE(NOW()),time(now()),NOW(),NOW());#Specify date or time 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 from the test table above, let's learn how to write several common query statements:

Query by 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 by date or time range:

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

Query this month's data:

#Query create_time for this month's data select * from t_date where DATE_FORMAT(create_time, '%Y-% m')= DATE_FORMAT( CURDATE( ) , '%Y-% m');

Query the most recent days of data:

#Query data for the last 7 or 30 days with date_col SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 7 DAY)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.