How to query date and time in mysql

Preface:

In project development, some business table fields often use date and time types, and subsequent queries on such fields will also be involved. There are many requirements for date and time queries. This article briefly describes the standardized query method for date and time fields.

1. Overview of date and time types

The date and time types supported by MySQL are DATETIME, TIMESTAMP, DATE, TIME, and YEAR. The comparison of these types is as follows:

When it comes to selecting date and time field types, just choose the appropriate type based on 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. The following introduces how to use several related functions.

• The CURDATE and CURRENT_DATE functions have the same function, returning the current system date value.
• The two functions CURTIME and CURRENT_TIME have the same function, returning the current system time value.
• The NOW() and SYSDATE() functions are the same, returning the current system date and time values.
• UNIX_TIMESTAMP Get UNIX timestamp function, return an unsigned integer based on UNIX timestamp.
• FROM_UNIXTIME converts a UNIX timestamp to a time format and is the inverse function of UNIX_TIMESTAMP.
• TO_DAYS() takes a date value and returns the number of days since 0 AD.
• DAY() Gets the day of the specified date or time.
• DATE() Gets the day of the specified date or time.
• TIME() Gets the time of the specified date or time.
• MONTH Gets the month of 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 a date.
• The DATE_ADD and ADDDATE functions have the same functionality, both adding a specified time interval to a date.
• The DATE_SUB and SUBDATE functions have the same function, both of which subtract a specified time interval from a date.
• ADDTIME Time addition operation, add the specified time to the original time.
• SUBTIME Time subtraction operation, subtract 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 based on 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) returns the number of days between the start time date1 and the end time date2mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
  -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| col1 | col2 |
+------+------+
| 1 | -15 |
+------+------+

3. Standardized query of date and time fields

The above contents are all prepared for our query needs. In project requirements, filter queries are often performed based on date or time conditions. Sometimes such requirements vary. Let's learn how to write queries on date and time fields.

First, in order to make the query more accurate, the data should be inserted according to the specifications. For example, the year uses 4 digits, the day and month must be within a reasonable range, etc. For the convenience of testing, we create a table and insert some data.

CREATE TABLE `t_date` (
`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
`year_col` YEAR NOT NULL COMMENT 'Year',
`date_col` date NOT NULL COMMENT 'Date',
`time_col` time NOT NULL COMMENT 'time',
`dt_col` datetime NOT NULL COMMENT 'datetime time',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
 PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time test table';


# Select the current date or time for both date and 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 a specified 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 in the test table above, let's learn how to write several common query statements:

Search by date or time etc.

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';

Search 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 the data of create_time in this month select * from t_date where DATE_FORMAT(create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' );

Query the data for the last few days

# Query the data of the last 7 days or 30 days based on date_col 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

# Query today's data SELECT * FROM t_date WHERE TO_DAYS(create_time) = TO_DAYS(NOW());

# Query the data of a certain month SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y-%m')='2020-06';

# Query data of a certain year SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y') = 2020;
SELECT * FROM t_date WHERE YEAR(create_time) = 2020;

# Query data according to date range and sort SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y') BETWEEN '2018' AND '2020' ORDER BY create_time DESC;

Summarize:

This article starts with the date and time fields, then describes how to use related functions, and finally lists some commonly used query methods. I hope this content is helpful to you. In real life, some queries may be more complex. Especially when the amount of data is large, queries based on time fields are often slow. In this case, you should also pay attention to creating indexes. It is best to convert the time field into a timestamp, because integer queries and filtering will be faster. It is also a good idea to remind you not to perform calculations on date and time fields, and not to do things at the database level that can be done by the program.

The above is the details of how to query the date and time in MySQL. For more information about querying the date and time in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!