sid sid - 6 months ago 7
SQL Question

Get count of test runs along with the time when it started

I have a table say test_data which looks like this

test_id | test_timestamp | test_value
123 | 2016-05-27 14:23:57.634119 | 45
123 | 2016-05-27 14:23:57.634119 | 11
123 | 2016-05-27 14:23:57.634119 | 12
123 | 2016-05-27 14:23:57.634119 | 13
123 | 2016-05-27 14:33:59.634121 | 46
123 | 2016-05-27 14:33:59.634121 | 50
456 | 2016-05-27 11:03:00.000000 | 14
456 | 2016-05-27 11:13:00.000000 | 15
456 | 2016-05-27 11:23:00.000000 | 16
456 | 2016-05-27 11:33:00.000000 | 17
123 | 2016-05-27 14:43:59.634121 | 47
123 | 2016-05-27 14:53:59.634121 | 48
123 | 2016-05-27 15:03:59.634121 | 49
123 | 2016-05-27 15:13:59.634121 | 46


Each test collects bunch of result at a given timestamp. I would like my result to be the test_id, start time of the test, count of the test_id,

Something like this

test_id | test_timestamp | count
123 | 2016-05-27 14:23:57.634119 | 10
456 | 2016-05-27 11:03:00.000000 | 4


Currently I have query which gives me two different outputs

SELECT test_id, COUNT(*) FROM test_data group by test_id;


This query gives me count of all the unique test_id along with count of them but doesn't give me the time when it started collecting the result. I am looking for the very first time when the test starts collecting the result.

test_id | count
123 | 10
456 | 4


The second query

SELECT test_id, COUNT(*), test_timestamp FROM test_data group by test_id ,test_timestamp order by test_timestamp asc;


This query gives me the count of test_id for each run.

test_id | test_timestamp | count
456 | 2016-05-27 11:03:00.000000 | 1
456 | 2016-05-27 11:13:00.000000 | 1
456 | 2016-05-27 11:23:00.000000 | 1
456 | 2016-05-27 11:33:00.000000 | 1
123 | 2016-05-27 14:23:57.634119 | 4
123 | 2016-05-27 14:33:59.634121 | 2
123 | 2016-05-27 14:43:59.634121 | 1
123 | 2016-05-27 14:53:59.634121 | 1
123 | 2016-05-27 15:03:59.634121 | 1
123 | 2016-05-27 15:13:59.634121 | 1


I can probably combine the output of the two sqls, but it would be great if I can get to the result in a single psql query. The table size is in the order of millions of rows.

Answer

Selecting the MIN() timestamp should work, this will be per group:

SELECT test_id, min(test_timestamp) as "start time", COUNT(*) FROM test_data group by test_id;

Here is a functional example with your data

It could be fun to add a column for Duration next to start time:

SELECT 
 test_id
 , min(test_timestamp ) as "start time"
 ,timestampdiff(MINUTE,min(test_timestamp ),max(test_timestamp )) as "Duration (Min)"
 ,COUNT(*) 
 FROM test_data group by test_id;

Sample