Data is extracted in minutes and averaged. Unit data is extracted.
Q:
There is now a table with the content of one entry every 10 seconds (6x60x24 = 8640 entries per day ), what I want to do now is to take data (12*24 = 288 records per day) at a time point in 5 minutes, and retrieve all values that meet the conditions at a time. What should I do?
A: The SQL statement is as follows:
Data extraction per minute:
Select node_id, avg (ad1_value) as AD1_VALUE, avg (ad2_value) as AD2_VALUE, date_format (collect_date, '% Y-% m-% d % H: % I') as collect_date
From realtime_data_3
Where collect_date> '2017-12-06 12:00:00'
And collect_date <'2017-12-07 12:00:00'
Group by date_format (collect_date, '% Y-% m-% d % H: % I ');
5. Unit: select floor (recDt/500) * 500 as gt, avg (nowValue) from reg_conditigroup by gt order by gt;
10. Unit: select floor (recDt/1000) * 1000 as gt, avg (nowValue) from reg_conditigroup by gt order by gt;
The date_format syntax is as follows:
Definition and usage
The DATE_FORMAT () function is used to display date/time data in different formats.
Syntax
DATE_FORMAT(date,format)
DateThe parameter is a valid date.FormatSpecifies the output format of the date/time.
The following formats are available:
Format |
Description |
% |
Abbreviated name of a week |
% B |
Abbreviated month name |
% C |
Month, Value |
% D |
Day of the month with an English prefix |
% D |
Day of the month, numeric value (00-31) |
% E |
Day of the month, value (0-31) |
% F |
Microseconds |
% H |
Hour (00-23) |
% H |
Hour (01-12) |
% I |
Hour (01-12) |
% I |
Minute, value (00-59) |
% J |
Day of the year (001-366) |
% K |
Hour (0-23) |
% L |
Hour (1-12) |
% M |
Month name |
% M |
Month, value (00-12) |
% P |
AM or PM |
% R |
Time, 12-hour (hh: mm: ss AM or PM) |
% S |
Seconds (00-59) |
% S |
Seconds (00-59) |
% T |
Time, 24-hour (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 name |
% W |
Day of the week (0 = Sunday, 6 = Saturday) |
% X |
Year, where Sunday is the first day of the week, four digits, used with % V |
% X |
Year, where Monday is the first day of the week, 4 digits, used with % v |
% Y |
Year, 4 digits |
% Y |
Year, two places |
Instance
The following script uses the DATE_FORMAT () function to display different formats. NOW () is used to obtain the current date/time:
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')DATE_FORMAT(NOW(),'%m-%d-%Y')DATE_FORMAT(NOW(),'%d %b %y')DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
The result is similar:
Dec 29 2008 11:45 PM12-29-200829 Dec 0829 Dec 2008 16:25:46.635