Mysql Query time period method _mysql

This article describes the MySQL query time period method. Share to everyone for your reference. The specific methods are as follows:

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

MySQL Time field has date, times, DateTime, timestamp, and so on, often we store the data in the time of the whole existence of a field, using datetime type; it is possible to separate dates and times, where a field stores date, A field stores time. No matter how stored, in the actual application, there is likely to be a "time period" type of query, such as a database of access records, you need to count the number of visits per day, this is a time period. Here are two common methods for MySQL query time periods, and other databases can be similar to implementations.

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

Copy Code code as follows:

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

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

method Two: Unix timestamp , each time corresponds to a unique UNIX timestamp, the timestamp is from ' 1970-01-01 00:00:00 ' for 0 start time, 1 per second increase. MySQL has built-in interchange functions for traditional time and Unix time, respectively:

Unix_timestamp (DateTime)
From_unixtime (Unixtime)

such as running

Copy Code code as follows:

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

Return 1267372800

Run

Copy Code code as follows:

SELECT From_unixtime (1267372800)

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

So, we can replace the data in the Time field with the Unix time of the whole type, so that the comparison time becomes an integer comparison, the index can greatly improve the efficiency. In the query, you need to convert the start time and the end time to the Unix time and then compare, for example:

Copy Code code as follows:

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

It is also possible to convert the Unix time into MySQL in the calling program, which in turn facilitates quick query time, although the display time needs to be reversed again.

I hope this article is helpful to the design of MySQL database.