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

How to query the date and time field in mysql, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

In project development, date and time types are often used in some business table fields, and subsequent queries for such fields are involved. There are also many kinds of requirements about date and time query. This article briefly talks about the standardized query method of date and time field.

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 several types is as follows:

Cdn.nlark.com/yuque/0/2020/png/119537/1591348516345-fe9c2cd5-2010-4a64-a152-18d36854622a.png ">

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

two。 Date and time correlation function

There are many functions that deal with date and time fields, some of which are often used in queries. Here are some of the related functions.

The CURDATE and CURRENT_DATE functions do the same and return the date value of the current system.

The CURTIME and CURRENT_TIME functions do the same and return the time value of the current system.

The NOW () and SYSDATE () functions do the same, returning 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 the UNIX timestamp into a time format, which is an inverse function to UNIX_TIMESTAMP.

TO_DAYS () extracts the date value and returns the number of days from the year AD 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 in the specified date.

The week ordinal of the year in which the WEEK gets the specified date.

YEAR gets the year.

QUARTER gets the quarterly value of the date.

The DATE_ADD and ADDDATE functions have the same function, adding a specified time interval to the date.

The DATE_SUB and SUBDATE functions have the same function, subtracting the specified time interval from the date.

ADDTIME time addition operation to add a specified time to the original time.

SUBTIME time subtraction operation, subtracting 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 based on the parameter.

Some examples of use:

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 purl 09 TO_DAYS 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 date2 mysql > SELECT DATEDIFF ('2017-11-30) AS COL1,-> DATEDIFF (' 2017-11-30) AS col2 +-+ | COL1 | col2 | +-+-+ | 1 |-15 | +-+-+ 3. Standard query for date and time fields

The above content is to prepare for our query requirements, in the project requirements, often take the date or time as the condition to filter the query. Sometimes there are a variety of requirements, so let's learn how to write queries about the date and time fields.

First of all, in order to make the query more accurate, the data should also be inserted according to the specification. For example, the year uses 4 digits, the date and month should be within a reasonable range, and so on. For the convenience of testing, we create a table and insert some data.

CREATE TABLE `tdate` (`dt_ id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `year_ col`YEAR NOT NULL COMMENT 'year', `date_ col`date', `time_ col`date', `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' # choose the 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 ()) # insert INSERT INTO t_date (`time_ col`, `date_ col`, `time_ col`, `dt_ col`, `create_ time`) INSERT INTO t_date (2020, '2020-06-03) VALUES (0900) at the specified date or time,' 2020-06-03 10 date_ 04mm, '2020-06-03 10 purr 04purr 04'), (2020, '2020-05-10),' 1815-00012, '2020-05-10 1616lv 0000') (2019, '2019-10-03,' 2019-10-03,'16-04, 2019-10-03, '2019-10-03, 1600-00), (2018,' 2018-06-03,'16-0-06-03), (2018-06-03, 2018-06-03 16-00), (2000) '2000-06-03,' 16-0-06-03-04, '2000-06-03-03-08,' 2000-06-03-03-08-00, (2008, '2008-06-03,' 16-0-04, '2008-06-03-03-08, 00-00,' 2008-06-03-03), (1980, '1980-06-03,' 16-04-04'), (1980, '1980-06-03),' 16-06-03 '1980-06-03 08PLR 001B,' 1980-06-0308RV 001L')

Based on the data from the above test table, let's learn how to write the following common query statements:

Query based on date or time equivalent

Select * from t_date where year_col = 2020 position select * from t_date where date_col = '2020-06-03 candidate select * from t_date where dt_col =' 2020-06-03 16 purse 04purl 04'

Query based on date or time range

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

Query this month's data

# query create_time 's data of this month select * from t_date where DATE_FORMAT (create_time,'% YMY% m') = DATE_FORMAT (CURDATE (),'% YMY% m')

Query the data of the last few days

# query the data of the last 7 or 30 days with date_col * 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.