MySql query time period method sample code

This article mainly introduces the method of querying time period in MySql, including the traditional query method for time field and UNIXtimestamp query skills, which is of great practical value , Friends in need can refer to the following

The example in this article describes the method of querying the time period in MySql. Share it with everyone for your reference. The specific methods are as follows:

Not everyone may know how to query the time period in MySql. Here are two methods of querying the time period in MySql for your reference.

The time fields of MySql include date, time, datetime, timestamp, etc. When we store data, we often store the entire time in one field, using the datetime type; we may also separate the date and time, that is, a One field stores date, and one field stores time. No matter how it is stored, in actual applications, there are likely to be queries containing "time period" types. For example, an access record database needs to count the number of visits per day, and each day is a time period. The following introduces two common methods for querying time periods in MySql. Other databases can also be implemented similarly.

Method 1: Traditional method, that is, specify the start time and end time, and use "between" or "<", ">" to establish conditions, such as querying March 2010 For the number of data items from the 1st to March 2nd, 2010, you can use

The code is as follows:

select count(*) from sometable where datetimecolumn>=&#39;2010-03-01 00:00:00&#39; and datetimecolumn<&#39;2010-03-02 00:00:00&#39;

However, this method is not integer## because the time is not # type data, so the comparison is less efficient, so if the amount of data is large, the time can be converted into an integer UNIX timestamp. This is method two.

Method 2: UNIX timestamp, each time corresponds to a unique UNIX timestamp, the timestamp is from '1970-01-01 00:00:00' to 0 Start timing and increase by 1 every second. MySql has built-in functions for the exchange of traditional time and UNIX time, which are:

UNIX_TIMESTAMP(datetime)

FROM_UNIXTIME(unixtime)

For example, run

The code is as follows:

SELECT UNIX_TIMESTAMP('2010-03-01 00:00:00')

Return 1267372800

Run

The code is as follows:

SELECT FROM_UNIXTIME(1267372800)

Return '2010-03-01 00:00:00 '

So, we can replace the data in the time field with integer UNIX time. In this way, the comparison time becomes an integer comparison. After establishing an

index, the efficiency can be greatly improved. When querying, you need to convert the start time and end time to UNIX time respectively before comparison, such as:

The code is as follows:

select count(*) from sometable where datetimecolumn>=UNIX_TIMESTAMP(&#39;2010-03-01 00:00:00&#39;) and datetimecolumn<UNIX_TIMESTAMP(&#39;2010-03-02 00:00:00&#39;)

You can also convert it first in the calling program Pass the UNIX time into MySql. In short, this method is helpful for quickly querying the time period, but the display time needs to be reversed again.

The above is the detailed content of MySql query time period method sample code. For more information, please follow other related articles on the PHP Chinese website!