MySQL Time group query

Table Tester

Field: ID--INT

Date--TIMESTAMP

　　

1, how to search by year, month and day group?

Select Date_format (date, '%y-%m-%d ') time, COUNT (*) count from TESTER Group by (date), month (date), day (date);

Where year (), month (), day () are extracted from the date, the month, Date field, respectively.

2, Time group query efficiency?

When I do not index, I test 100W rows of data, using the following several ways to get the performance:

NO INDEX Select Date_format (DATE, '%y-%m-%d ') time, COUNT (*) count from TESTER Group by time Returned in 1.315 sec
NO INDEX Select Date_format (DATE, '%y-%m-%d ') time, COUNT (*) count from TESTER Group by year (time), Month (time ), day (time) returned in 1.045 sec
NO INDEX Select Date_format (date, '%y-%m-%d ') time, COUNT (*) count from TESTER Group by year (date), Month (date ), day (date) returned in 0.624 sec

　　

Obviously, using the date field directly is optimal. After the index is established:

INDEX on date Select Date_format (date, '%y-%m-%d ') is time,count (*) count from TESTER Group by (date), month (date), Day (dat e) returned in 0.578 sec

The difference between finding and not indexing is not too big, but when the amount of data reaches tens, the upgrade is still quite impressive.

3. What do I do if I need to return to Unix time?

The test is performed in the non-indexed mode:
Return seconds: Select Unix_timestamp (Date_format (DATE, '%y-%m-%d ')) time,count (*) Count from Svndb. Tloctask GROUP BY (date), month (date), Day returned in 0.640 sec
Returns milliseconds: Select Unix_timestamp (Date_format (DATE, '%y-%m-%d ')) * time,count (*) Count from Svndb. Tloctask GROUP BY (date), month (date), Day returned in 0.640 sec

Visible, performance is good, you can not pull to the background to calculate.

4. About Date_format (Date,format) function
Formats the date value according to the format string. The following modifiers can be used in the format string:
%M month name (January ... December)
%W Week name (Sunday ... Saturday)
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）
%Y year, number, 4 bit
%y year, number, 2 bit
%a abbreviated weekday name (Sun ... Sat)
Number of days in the month of%d, number (00 ...). 31)
Number of days in%e month, number (0 ... 31)
%m Month, number (01 ... 12)
%c month, number (1 ... 12)
%b Abbreviated month name (Jan ... DEC)
%j Days of the year (001 ... 366)
%H hours (00 ... 23)
%k hours (0 ... 23)
%h hours (01 ... 12)
%I Hours (01 ... 12)
%l hours (1 ... 12)
%i minutes, Numbers (00 ... 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ... 59)
%s seconds (00 ... 59)
%p am or PM
%w days in one weeks (0=sunday ... 6=saturday)
%u Week (0 ... 52), here Sunday is the first day of the week
%u Week (0 ... 52), here Monday is the first day of the week
Percent of a text "%".

5, MySQL time can be directly subtracted from it?

The answer is no, such as the table has time1, time2 two fields, are the time type, want to calculate the difference between them (in seconds)

Time1=2014-01-01

time2=2014-01-02

When using this judging condition where (Time2-time1 > 36), the pit is trampled, and MySQL does not subtract the real time, but instead of stitching up the string to subtract ' 20140101 '-' 20140102 ', the result is meaningless.

So when you want to calculate the time difference, there are three ways:

Time_to_sec (Timediff (T2, T1))

Timestampdiff (second, T1, T2)--note, here T1 in front

Unix_timestamp (T2)-Unix_timestamp (T1) will be able to get the correct time difference in t2-t1 units in seconds. 6.A tips: How to convert the time format of JS string to date? To use 2014/10/13 00:00:00 or 2014/10/13 0:0:0 or 2014/10/13 such format can be completed correctly, such as New Date (' 2014/10/13 '), otherwise in IE browser, will get the wrong conversion results.

Well, this time the blog is relatively short, using the form of question and answer, see the effect bar.

　　Reprint Please specify original site:http://www.cnblogs.com/lekko/p/4023113.html

MySQL Time group query