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

Question

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 1

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

Answer 2

I think you are looking for min():

SELECT test_id, COUNT(*), MIN(test_timestamp)
FROM test_data 
GROUP BY test_id 
ORDER BY MIN(test_timestamp) asc;