I have a table in the following form:
> select * from speedTest_street_route;
name: speedTest_street_route
time count git_branch git_buildtime git_commit mean measurement_environment metric_type sum tags upper
---- ----- ---------- ------------- ---------- ---- ----------------------- ----------- --- ---- -----
1641907345041000000 72 dev-2.x 2022-01-11T13:11:11+0000 40abc39e77471a06643c3e299204bc81bde7a662 19.103416 CI histogram 1375.445932 112.5722
1641912471818000000 72 dev-2.x 2022-01-11T14:36:42+0000 10e407c2111350c3798f082a5567738a9ad2a691 19.290892 CI histogram 1388.944241 124.977444
1641982582015000000 72 dev-2.x 2022-01-12T10:04:50+0000 10e407c2111350c3798f082a5567738a9ad2a691 19.247772 CI histogram 1385.839555 121.305931
1641984197645000000 72 dev-2.x 2022-01-12T10:32:21+0000 10e407c2111350c3798f082a5567738a9ad2a691 18.461079 CI histogram 1329.197706 104.063253
1641993853234000000 72 dev-2.x 2022-01-12T13:13:07+0000 10e407c2111350c3798f082a5567738a9ad2a691 18.520056 CI histogram 1333.444008 108.741783
1641994830843000000 72 dev-2.x 2022-01-12T13:29:25+0000 10e407c2111350c3798f082a5567738a9ad2a691 19.135734 CI histogram 1377.772854 123.733228
1642084970215000000 72 dev-2.x 2022-01-13T14:31:31+0000 95d6212886ea1c127306ecc4ce105428a28f4b28 18.358699 CI histogram 1321.826311 125.793374
1642086190659000000 72 dev-2.x 2022-01-13T14:51:42+0000 0be885d0ed2cf8f478a395f8ed2f07dd4cf19357 18.545847 CI histogram 1335.301013 107.343941
1642158554845000000 72 dev-2.x 2022-01-14T10:58:14+0000 8dc91ab84a448d31931d8355bff64908c0fb3f63 18.476948 CI histogram 1330.340253 118.418691
1642500404661000000 72 dev-2.x 2022-01-18T09:55:30+0000 2188c98a45a5660e44ee297100d1a3461bbfda86 18.447016 CI histogram 1328.185139 117.829735
1642501188019000000 72 dev-2.x 2022-01-18T10:08:40+0000 d848e55d5a18434e9d4c808ee234503e5a074d54 18.609775 CI histogram 1339.903792 111.130132
1642691379044000000 72 dev-2.x 2022-01-20T14:58:34+0000 68630e23d653d98a94be976afbd2686587aec6c9 18.766944 CI histogram 1351.219946 105.414342
1642692335470000000 72 dev-2.x 2022-01-20T15:14:26+0000 c46bc0de3b2c34d0e9647e8943e2273e58934f6e 19.305548 CI histogram 1389.999463 111.578499
1642695549682000000 72 dev-2.x 2022-01-20T16:07:58+0000 05c816d63b6da59fc3f1ad08c9003af7f7e45722 19.262485 CI histogram 1386.898895 107.516701
1643104800212000000 64 dev-2.x 2022-01-25T09:49:05+0000 6832585b2e1eda88785e60642e283eb0b21e1315 18.453888 CI histogram 1181.048849 105.449233
1643104804417000000 72 dev-2.x 2022-01-25T09:49:05+0000 6832585b2e1eda88785e60642e283eb0b21e1315 18.474951 CI histogram 1330.19644 105.449233
1643105564882000000 72 dev-2.x 2022-01-25T10:01:44+0000 a39d35391bfbbee99d8b1f811ac96b519581148f 19.541262 CI histogram 1406.97089 118.347097
1643106396828000000 72 dev-2.x 2022-01-25T10:15:14+0000 bc6cc6d84d5971cf52f90e4ec107927b7c899a27 18.513493 CI histogram 1332.971461 129.106839
1643107158260000000 72 dev-2.x 2022-01-25T10:28:18+0000 9534f66ee4c945bf48eb4d4ef4252c8d5358bcf8 18.79588 CI histogram 1353.303382 107.356706
1643107939053000000 72 dev-2.x 2022-01-25T10:40:58+0000 8877b9c2ec8cc713158ff4bad33af3349566f1d5 19.522368 CI histogram 1405.610505 118.113306
1643201365595000000 72 dev-2.x 2022-01-26T12:38:24+0000 f0e57cb135f9f5a33bbbbae92966f606f1e983b5 19.063058 CI histogram 1372.540175 126.823212
1643222165037000000 72 dev-2.x 2022-01-26T18:25:12+0000 540df18dd4797ca526c51eefd36eade0b59fcc8b 18.102997 CI histogram 1303.415758 91.592848
1643293104247000000 72 dev-2.x 2022-01-27T14:06:57+0000 8551c54bb75fab4886fc9b4cd9d5e2453e45c3b1 18.447627 CI histogram 1328.22916 123.368928
1643712665997000000 72 dev-2.x 2022-02-01T10:40:00+0000 46f44a97a4031267471ce56b82de202d35c3bb78 19.444352 CI histogram 1399.993313 118.39801
1643713436057000000 72 dev-2.x 2022-02-01T10:52:49+0000 3d938eca14c5fec49f0a90275e7059221878f4f5 19.110381 CI histogram 1375.947428 110.996614
1643720912069000000 72 dev-2.x 2022-02-01T12:57:33+0000 d4aecb6bba9945983dc29cf711bd0f2739ffb09b 19.660106 CI histogram 1415.527618 119.394692
1643893606729000000 72 dev-2.x 2022-02-03T12:55:32+0000 eb487b8231cafc44b458d1727cac160272b60eb7 18.757551 CI histogram 1350.543664 111.888364
1643894877727000000 72 dev-2.x 2022-02-03T13:16:47+0000 392d24c936e37eb5c37eabfb1cfac2f37ed5e3c1 18.637119 CI histogram 1341.872597 114.893234
1643898255989000000 72 dev-2.x 2022-02-03T14:12:48+0000 c5e8e858e429df8b7cb27fe585a57b9aa3fca591 18.16809 CI histogram 1308.102471 92.762293
1643899054192000000 72 dev-2.x 2022-02-03T14:26:03+0000 4da3ea3d2801ebb8eeff166a6d7ce2e1a6b32594 18.016132 CI histogram 1297.161468 97.374142
1643899800214000000 41 dev-2.x 2022-02-03T14:39:12+0000 ca92dce3d3854905f9df92c7ba3d8d93cfddfb14 20.867515 CI histogram 855.568117 116.622181
1643899813854000000 72 dev-2.x 2022-02-03T14:39:12+0000 ca92dce3d3854905f9df92c7ba3d8d93cfddfb14 19.049012 CI histogram 1371.528877 116.622181
1643900597352000000 72 dev-2.x 2022-02-03T14:51:54+0000 3c1dd9a84333ae97c4007da6233dc4727d7cd630 19.124347 CI histogram 1376.953006 111.613237
1643901368153000000 72 dev-2.x 2022-02-03T15:04:56+0000 72e9333252f35ce9706d27bb6b63d2ba660e7887 18.397149 CI histogram 1324.59474 116.457198
1643902143103000000 72 dev-2.x 2022-02-03T15:17:51+0000 4d2cc5a626f594488fa11546f9a0dfa837e4e9aa 19.010819 CI histogram 1368.779001 107.457925
1643902895789000000 72 dev-2.x 2022-02-03T15:30:41+0000 f1afb1c04563708eb0f835d12c8f848e58de9b2f 18.679367 CI histogram 1344.91441 109.95443
1643952223892000000 72 dev-2.x 2022-02-04T05:12:43+0000 a1db05891472bdd728459881d56c448377af2be8 19.061332 CI histogram 1372.415874 106.467792
1643958969959000000 72 dev-2.x 2022-02-04T07:04:54+0000 8d9d415c362d0cdd36e9ecdfed27f3517548de23 18.683503 CI histogram 1345.212217 113.421712
1643965275879000000 72 dev-2.x 2022-02-04T08:50:02+0000 3ca693d6bffa8baa1fe0ec6610513dabb8e6e35b 19.376158 CI histogram 1395.083363 124.484586
1643966401539000000 36 dev-2.x 2022-02-04T09:08:52+0000 f04f1c942bbe7e7e18dd9d4f14b9689af5be0aa8 19.950961 CI histogram 718.234586 110.913669
1643966416502000000 72 dev-2.x 2022-02-04T09:08:52+0000 f04f1c942bbe7e7e18dd9d4f14b9689af5be0aa8 18.418034 CI histogram 1326.098425 110.913669
1643968507680000000 72 dev-2.x 2022-02-04T09:44:03+0000 ac0b8b4f1d26d05a9c8ebaafa387f622f205dcb2 19.171212 CI histogram 1380.327248 105.270737
1643970037781000000 72 dev-2.x 2022-02-04T10:09:26+0000 cd74054c5ec226b1131b6a55dbfc42bf5d391e82 18.683617 CI histogram 1345.220429 99.942457
1643984068470000000 72 dev-2.x 2022-02-04T14:03:19+0000 d460e6217ce7790a6e392f63996da37dbe4a4f8e 18.886261 CI histogram 1359.810757 119.414846
1643994543976000000 72 dev-2.x 2022-02-04T16:57:57+0000 7304f4b32b779e829ab7e7a357beb5cbfd5b943e 18.643782 CI histogram 1342.352328 111.397173
1644312433710000000 72 dev-2.x 2022-02-08T09:15:57+0000 2bb6f62f34f32575c81c17d28086887a10f596df 18.564456 CI histogram 1336.640851 119.941155
1644313928927000000 72 dev-2.x 2022-02-08T09:40:57+0000 5fd2bfd6ae1b7c7323624eee40b2a1cc7480427e 18.226081 CI histogram 1312.27785 91.50364
1644314719459000000 72 dev-2.x 2022-02-08T09:53:55+0000 11678fc96b8bb0acaf5ff1fde248e25457a14378 18.802741 CI histogram 1353.79737 108.331542
1644316312282000000 72 dev-2.x 2022-02-08T10:20:48+0000 dcdb6acf95002266df37b7824034ba0d56f98023 18.861821 CI histogram 1358.051107 105.478498
1644324264221000000 10 speed-test-modes 2022-02-08T13:17:38+0100 13c34ef6c38a9047c9fc2379e555b3cfc66aedbb 76.665767 local histogram 766.657672 transit 154.05099
1644324264222000000 170 speed-test-modes 2022-02-08T13:17:38+0100 13c34ef6c38a9047c9fc2379e555b3cfc66aedbb 15.033155 local histogram 2555.636423 stop-to-stop,transit 137.046422
1644324847984000000 10 speed-test-modes 2022-02-08T13:17:38+0100 13c34ef6c38a9047c9fc2379e555b3cfc66aedbb 81.68445 local histogram 816.844497 transit 207.076885
1644324847985000000 170 speed-test-modes 2022-02-08T13:17:38+0100 13c34ef6c38a9047c9fc2379e555b3cfc66aedbb 15.743064 local histogram 2676.320815 stop-to-stop transit 207.853391
It is data collected by a performance bench mark of the software I’m developing.
I would like to group the performance metrics by the column git_commit and use the following query:
SELECT mean(mean) FROM "speedTest_street_route" WHERE count > 0 AND measurement_environment = 'CI' AND success != 'false' GROUP BY git_commit;
However, this returns results like this:
name: speedTest_street_route
tags: git_commit=f1afb1c04563708eb0f835d12c8f848e58de9b2f
time mean
---- ----
0 18.679367
Since I would like to graph this data in Grafana I need to have a time column but the results always have the timestamp 0. I understand why InfluxDB doesn’t return one since it’s not obvious which one you should choose.
In my case all the rows are quite close to one another so you could choose either the first or the last one (or any time in between).
So I added last(time) to the query
SELECT mean(mean), first(time) FROM "speedTest_street_route" WHERE count > 0 AND measurement_environment = 'CI' AND success != 'false' GROUP BY git_commit;
But that didn’t work either. The time is still 0.
Does anyone know how I can achieve my goal?
