Mysql 5.5 aggregate time intervals -

-

i have seen plenty of posts how specify time interval. tried use them strange results. table:

select value,time mysensor9 order time desc;  +-------+---------------------+ | value | time                | +-------+---------------------+ |    79 | 2016-01-27 22:19:46 | |    45 | 2016-01-27 22:19:45 | |     5 | 2016-01-27 22:19:44 | |    72 | 2016-01-27 22:19:43 | |    20 | 2016-01-27 22:19:42 | |    92 | 2016-01-27 22:19:41 |  .....

i have filled table values every second of month.

then try aggregate data of table every 5min/ hour/day/month. when try aggregate average value per day make query:

select avg(value),time mysensor9 time > "2015-12-09" group unix_timestamp(time) div (3600*24) order time asc;

the results ok:

+------------+---------------------+ | avg(value) | time                | +------------+---------------------+ |    48.7179 | 2015-12-09 02:13:46 | |    49.4044 | 2015-12-10 02:13:46 | |    49.5001 | 2015-12-11 02:13:46 | |    49.4805 | 2015-12-12 02:13:46 | |    48.9036 | 2015-12-13 02:13:46 |

when want aggregate per 1 hour interval make query:

select avg(value),time mysensor9 time > "2015-12-09" group unix_timestamp(time) div (3600) order time asc;

edit(hour interval results):

+------------+---------------------+ | avg(value) | time                | +------------+---------------------+ |    49.2355 | 2015-12-09 00:13:46 | |    48.0028 | 2015-12-09 01:13:46 | |    49.6316 | 2015-12-09 02:13:46 | |    47.8449 | 2015-12-09 03:13:46 | |    49.0166 | 2015-12-09 04:13:46

|

and again results ok. when want aggregate 5 minute intervals using same approach:

select avg(value),time mysensor9 time > "2015-12-09" group unix_timestamp(time) div (300) order time asc;

i this:

+------------+---------------------+ | avg(value) | time                | +------------+---------------------+ |    44.9865 | 2015-12-09 00:13:46 | |    50.3310 | 2015-12-09 00:15:00 | |    50.0135 | 2015-12-09 01:13:46 | |    47.4843 | 2015-12-09 01:15:00 | |    51.8514 | 2015-12-09 02:13:46 |

why happening? why not return this:

+------------+---------------------+ | avg(value) | time                | +------------+---------------------+ |    44.9865 | 2015-12-09 00:13:46 | |    50.3310 | 2015-12-09 00:18:46 | |    50.0135 | 2015-12-09 00:23:46 | |    47.4843 | 2015-12-09 00:28:46 | |    51.8514 | 2015-12-09 00:33:46 |

when select thats not part of group pretty random mysql selects (it select 1 of grouped rows).

you should select same value grouped , format date:

select avg(value),date_format(from_unixtime((unix_timestamp(time) div (300))*300),'%d-%m-%y %h:%i:%s') group_time mysensor9 time > "2015-12-09" group unix_timestamp(time) div (300) order time asc;

you use min() / max() upper or lower end of grouped rows, if practical case seems bit more elegant:

select avg(value),min(time) mysensor9 time > "2015-12-09" group unix_timestamp(time) div (300) order time asc;

also note selecting non group columns in group context mysql behaviour, other databases state syntax error.


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more