MySQL time group query, MySQL time group

MySQL time group query, MySQL time group

Table TESTER

Field: id -- INT

Date -- TIMESTAMP

　　

1. How do I query data by year, month, or day group?

Select DATE_FORMAT (date, '% Y-% m-% D') time, count (*) count from TESTER group by year (date), month (date ), day (date );

Year (), month (), and day () are extracted from the year, month, and day fields.

 

2. How efficient is time group query?

When I did not create an index, I tested million rows of data and used the following methods to obtain the performance:

No index select DATE_FORMAT (date, '% Y-% m-% D ')Time, Count (*) count from TESTER groupTimeReturnein 1.315 sec
No index select DATE_FORMAT (date, '% Y-% m-% D ')Time, Count (*) count from TESTER groupYear (time), month (time), day (time)Returnein 1.045 sec
No index select DATE_FORMAT (date, '% Y-% m-% D ')Time, Count (*) count from TESTER groupYear (date), month (date), day (date)Returnein 0.624 sec

　　

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

Index on date select DATE_FORMAT (date, '% Y-% m-% D') time, count (*) count from TESTER group by year (date), month (date ), day (date) returned in 0.578 sec

The time difference between the discovery and non-index creation is not too large. However, when the data volume reaches 10 million, the improvement is still considerable.

 

3. How to return Unix time?

The test is performed in the non-Index Mode:
Return Second: select UNIX_TIMESTAMP (DATE_FORMAT (date, '% Y-% m-% D') time, count (*) count from svndb. tlocTask group by year (date), month (date), day (time) returned in 0.640 sec
Returns millisecond: select UNIX_TIMESTAMP (DATE_FORMAT (date, '% Y-% m-% D '))*1000Time, count (*) count from svndb. TlocTask group by year (date), month (date), day (time) returned in 0.640 sec

It can be seen that the performance is good, and you do not need to go to the background for computing.

 

4. About the DATE_FORMAT (date, format) Function
Format the date value based on the format string. The following modifiers can be used in a format string:
% M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (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 a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

 

5. Can I directly Subtract time from each other in MySQL?

The answer is no. For example, the table contains two fields, time1 and time2, all of which are of the time type. to calculate the difference between them (in seconds)

Time1 = 2014-01-01

Time2 = 2014-01-02

When this condition where (time2-time1> 36) is used, MySQL will not subtract the real time, instead, it is concatenated to form a string that subtract '000000'-'000000'. This result has no practical significance.

To calculate the time difference, you can use the following three methods:

TIME_TO_SEC (TIMEDIFF (t2, t1 ))

TIMESTAMPDIFF (second, t1, t2) -- Note that t1 is in front

UNIX_TIMESTAMP (t2)-UNIX_TIMESTAMP (t1) returns the correct time difference for the t2-t1, in seconds. 6,One tips: How to convert the js string-type time format to Date?You can use a format like 00:00:00, or, for example, new Date ('2017/13 '). Otherwise, you will get an incorrect Conversion Result in the IE browser.

Well, this blog is short. I used the Q & A form to check the effect.

Reprinted please indicate the original address: http://www.cnblogs.com/lekko/p/4023113.html

Mysql queries by group and time conditions

Select bt. date, s1.cnt, s1.amt, s2.cnt, s2.amt, s3.cnt, s3.amt, s4.cnt, s4.amt from (select date from base_table group by date) bt
Left join (select count (status) cnt, sum (amount) amt, date from base_table where status = 1 group by status, date) s1
On bt. date = s1.date
Left join (select count (status) cnt, sum (amount) amt, date from base_table where status = 2 group by status, date) s2
On bt. date = s2.date
Left join (select count (status) cnt, sum (amount) amt, date from base_table where status = 3 group by status, date) s3
On bt. date = s3.date
Left join (select count (status) cnt, sum (amount) amt, date from base_table where status = 4 group by status, date) s4
On bt. date = s4.date

Teach you how to query mysql instances by time segment

Use the DATE_FORMAT function.
After formatting, The result contains only four parts: year, month, and day.
Then you can Group.

The following is an example and description of the DATE_FORMAT function.

Mysql> SELECT
-> DATE_FORMAT (NOW (), '% Y ~ % M ~ % D % k. % I. % s');
+ --------------------- +
| A |
+ --------------------- +
| 2010 ~ 10 ~ 22 2.1655.09 |
+ --------------------- +
1 row in set (0.00 sec)

% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (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 a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

All other characters are not interpreted and copied to the result.