The recent project needs to collect the total and average occurrence times of certain events/indicators within a period of time according to the statistics of each minute, several minutes and every day. Therefore, it summarizes the data related to time processing and statistics in Mysql.

Statistics are collected by minute during a certain time period

SELECT count(1), date_format(a5_firsttime, "%H:%i") as t from ccb_alerts group by t order by null
Copy the code

This method can be used to summarize statistics by day, working day (Monday to Friday), hour, minute, and month. Mysql output date format:

The output format meaning
%a Abbr. Sunday name
%b Abbreviated month name
%c Month, numerical
%D The day of the month with an English prefix
%d Day of the month, number (00-31)
%e Days of the month, values (0-31)
%f microseconds
%H Hours (00-23)
%h Hours (01-12)
%I Hours (01-12)
%i Minutes, values (00-59)
%j Day of the year (001-366)
%k Hours (0-23)
%l Hours (1-12)
%M Month of
%m Month, value (00-12)
%p AM or PM
%r Time, 12-hours (HH :mm: SS AM or PM)
%S Seconds (00-59)
%s Seconds (00-59)
%T Time, 24-hours (hh:mm:ss)
%U Week (00-53) Sunday is the first day of the week
%u Week (00-53) Monday is the first day of the week
%V Week (01-53) Sunday is the first day of the week, used with %X
%v Week (01-53) Monday is the first day of the week, used with %x
%W Week of
%w Day of the week (0= Sunday, 6= Saturday)
%X Year, where Sunday is the first day of the week, 4 bits, with %V used
%x Year, where Monday is the first day of the week, 4 bits, with %v used
%Y Four years,
%y Years, two

If you want to statistics within a day, according to a certain minute interval summary of the data, with SQL implementation, do not know what convenient way, if we have relevant solutions, you can share.

2017-05-31 Add support for cross-day statistics in accordance with a certain interval of SQL statistics summary number, example is in accordance with 15 minutes summary statistics

select count(1), from_unixtime( round(unix_timestamp(a5_firsttime)/(15*60)) * 15*60) from ccb_alerts where a5_firsttime > = '2017-03-20 00:00:00' group by round(unix_timestamp(a5_firsttime)/(15*60)) 
Copy the code

Date formatting function

Date_format (date, format) and time_format(time,format) can convert a date/time to various strings.

date_fromat(date, format)
Copy the code

TIMSTAMPADD

Calculate the date according to the interval entered.

timestampadd(unit,interval,datetime_expr)

select timestampadd(day.1.'the 2008-08-08 08:00:00'); - the 2008-08-09 08:00:00
Copy the code

TIMESTAMPDIFF

TIMESTAMPDIFF; TIMESTAMPDIFF;

grammar

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

instructions

Returns the integer difference between the date or datetime_expr1 and datetime_expr2THE expressions. The units of the result are given by the interval parameter. The legal values for interval are the same as those listed in the TIMESTAMPADD() function specification.

Mysql > Timstampdiff; Mysql > Timstampdiff; Mysql > Timstampdiff