php - Group by mongodate in mongodb -


my insertd data 

   {    "_id" : objectid("5992e6f52479d56b0b2709bf"),          "timestamp" : isodate("2017-08-15t12:19:25.680+0000"),          "record" : 20507361.0,          "battv_min" : 11.9,          "sevolt" : 0.0     }

and query getting date is

        $collection1 = $db->tablename;         $filter1 = array('timestamp' => array('$gte' => $acst1, '$lte' => $acst2));         $options1 = array('projection' => array('_id' => 0,'temperature' => 1,timestamp => 1));         $oresult = $collection1->find($filter1,$options1);

i getting data in billions want optimize query using group method, if have data below 70000 group second if have data range between 70000 100000 group minute on minute, time , last day.

you can use aggregate function use group in mongodb project date format want , if want column value use $addtoset function , project average value use avg function. 

$collection1 = $db->tablename;                 $oresult = $collection1->aggregate([                                 [                                     '$match' => array(timestamp => array('$gte' => $acst1, '$lte' => $acst2))                                 ],                                 [                                     '$project' => [                                     'year'  => ['$year' =>'timestamp'],                                         'month' => ['$month' => '$timestamp'],                                         'day'   => ['$dayofmonth' => '$timestamp'],                                         'hour'  => ['$hour' => '$timestamp'],                                         'minute'   => ['$minute' => '$timestamp'],                                         'second'   => ['$second' => '$timestamp'],                                         'millisecond'   => ['$millisecond' => '$timestamp'],                                             'value'   => '$battv'                                      ]                                 ],                                 [                                     '$group' => [                                         '_id'    => [                                             'year'  => '$year',                                             'month' => '$month',                                             'day'   => '$day',                                             'hour'  => '$hour',                                             'minute'   => '$minute',                                             'second' => '$second',                                             'millisecond' => '$millisecond'                                          ],                                         'count'  => ['$sum' => 1],                                         'values' => ['$addtoset' => '$value']                                     ]                                 ],                                  [                                     '$sort' => ['_id' => 1]                                 ],                                 [                                     '$project' => [                                         'a' => '$count',                                         'new' =>  ['$avg' => '$values'],                                         'y' => [                                             '$concat' => [                                                 ['$substr' => ['$_id.year', 0, 4]],                                                 '-',                                                 [                                                     '$cond' => [                                                         ['$lte' => ['$_id.month', 9]],                                                         [                                                             '$concat' => [                                                                 '0',                                                                 ['$substr' => ['$_id.month', 0, 2]],                                                             ]                                                         ],                                                         ['$substr' => ['$_id.month', 0, 2]]                                                     ]                                                 ],                                                 '-',                                                 [                                                     '$cond' => [                                                         ['$lte' => ['$_id.day', 9]],                                                         [                                                             '$concat' => [                                                                 '0',                                                                 ['$substr' => ['$_id.day', 0, 2]],                                                             ]                                                         ],                                                         ['$substr' => ['$_id.day', 0, 2]]                                                     ]                                                 ],                                                 ' ',                                                 [                                                     '$cond' => [                                                         ['$lte' => ['$_id.hour', 9]],                                                         [                                                             '$concat' => [                                                                 '0',                                                                 ['$substr' => ['$_id.hour', 0, 2]],                                                             ]                                                         ],                                                         ['$substr' => ['$_id.hour', 0, 2]]                                                     ]                                                 ],                                                 ':',                                                 [                                                     '$cond' => [                                                         ['$lte' => ['$_id.minute', 9]],                                                         [                                                             '$concat' => [                                                                 '0',                                                                 ['$substr' => ['$_id.minute', 0, 2]],                                                             ]                                                         ],                                                         ['$substr' => ['$_id.minute', 0, 2]]                                                     ]                                                 ],                                                 ':',                                                 [                                                     '$cond' => [                                                         ['$lte' => ['$_id.second', 9]],                                                         [                                                             '$concat' => [                                                                 '0',                                                                 ['$substr' => ['$_id.second', 0, 2]],                                                             ]                                                         ],                                                         ['$substr' => ['$_id.second', 0, 2]]                                                     ]                                                 ],                                                 '.',                                                 [                                                     '$cond' => [                                                         ['$lte' => ['$_id.millisecond', 9]],                                                         [                                                             '$concat' => [                                                                 '0',                                                                 ['$substr' => ['$_id.millisecond', 0, 1]],                                                             ]                                                         ],                                                         ['$substr' => ['$_id.millisecond', 0, 1]]                                                     ]                                                 ]                                              ]                                         ],                                     ]                                 ]                             ]                         );

Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more