How MySQL queries the time period

The example in this paper describes the method of MySQL query time period. Share to everyone for your reference. Here's how:

MySQL query time period of the method may not everyone, the following for you to introduce two methods of MySQL query time period, for your reference.

The time field of MySQL has date, times, datetime, timestamp and so on, often we store the data when the whole time there is a field, with the DateTime type, may also be used to separate the date and time, that is, a field store date, A field is stored in time. Regardless of how it is stored, in real-world applications, it is likely that a query with a "time period" type, such as a database of access records, needs to count the number of visits per day, which is a time period every day. Here are two common methods for MySQL query time periods, and other databases can be similar implementations.

method One: The traditional way , that is to specify the start time and end time, with "between" or "<", ">" to establish conditions, such as querying the number of data bars from March 1, 2010 to March 2, 2010, you can use

Select COUNT (*) from sometable where datetimecolumn>= ' 2010-03-01 00:00:00 ' and datetimecolumn< ' 2010-03-02 00:00:00 '

However, because time is not an integer data, this method is less efficient when compared, so if the amount of data is large, you can convert the time to a Unix timestamp of integer type, which is method two.

method Two: Unix timestamp, each time corresponding to a unique UNIX timestamp, the timestamp is starting from ' 1970-01-01 00:00:00 ' for 0 to start timing, increased by 1 per second. MySQL has built-in swap functions for traditional time and Unix time, respectively:

Unix_timestamp (DateTime)
From_unixtime (Unixtime)

such as running

Copy CodeThe code is as follows: SELECT unix_timestamp (' 2010-03-01 00:00:00 ')

Returns 1267372800

Run

SELECT From_unixtime (1267372800)

Back to ' 2010-03-01 00:00:00 '

As a result, we can replace the data in the Time field with the Unix time of the integer type, so that the comparison time becomes an integer comparison, and the index can greatly improve the efficiency. When querying, you need to convert the start time and end time to Unix time and then compare them, for example:

Select COUNT (*) from sometable where Datetimecolumn>=unix_timestamp (' 2010-03-01 00:00:00 ') and datetimecolumn< Unix_timestamp (' 2010-03-02 00:00:00 ')

You can also convert to Unix time in the calling program and then into MySQL, in short, this way is useful to quickly query the time period, but the display time needs to be reversed again.

It is hoped that this article will be helpful to the MySQL database design of everyone.

How MySQL queries the time period