How to query the time period in mysql

Mysql query time period method: 1. Traditional method, that is, specifying the start time and end time; 2. UNIX timestamp, each time corresponds to a unique UNIX timestamp, the function is [UNIX_TIMESTAMP(datetime )].

Mysql query time period method:

Method 1: Traditional method, that is Specify the start time and end time, and use "between" or "<", ">" to establish conditions. For example, to query the number of data items from March 1, 2010 to March 2, 2010, you can use

select count(*) from sometable where datetimecolumn>=&#39;2010-03-01 00:00:00&#39; and datetimecolumn<&#39;2010-03-02 00:00:00&#39;但是，这种方法由于时间不是整数型数据，所以在比较的时候效率较低，所以如果数据量较大，可以将时间转换为整数型的UNIX时间戳，这就是方法二。

Method 2: UNIX timestamp, each time corresponds to a unique UNIX timestamp, the timestamp starts from '1970-01-01 00:00:00' as 0 Timing, increasing by 1 every second. MySql has built-in interchange functions between traditional time and UNIX time, which are:

• UNIX_TIMESTAMP(datetime)

• FROM_UNIXTIME(unixtime)

For example, if you run

, the code is as follows:

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

Returns 1267372800

Run

SELECT FROM_UNIXTIME(1267372800)

Returns '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, which can be greatly improved after establishing an index. efficiency. When querying, you need to convert the start time and end time to UNIX time respectively before comparison, such as:

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 to UNIX time in the calling program and then pass it into MySql. In short, this method It is helpful to quickly query the time period, but the display time needs to be reversed again.

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of How to query the time period in mysql. For more information, please follow other related articles on the PHP Chinese website!