Downsample Timestamp in milisecond range in Postgres

Question

I have a table as below

timestamp                    v1    v2    v3
2020/01/01 08:10:20.300      10    20    30
2020/01/01 08:10:20.306      11    21    31
2020/01/01 08:10:20.310      12    22    32
2020/01/01 08:10:20.318      13    23    33
2020/01/01 08:10:20.325      14    24    34
.......................
2020/01/01 08:10:21.100      19    29    39

As can be seen that the timestamp does not change uniformly in the millisecond and has a lot of granular data. I am interested to condense the table such that, the timestamp and data columns average out for every 100 millisecond ie all the data between 100 millisecond gets averaged out.

I have a solution that seems to be working but the average is over every second and not after every 100 millisecond.

SELECT date_trunc('second', timestamp) AS timestamp
      ,avg(v1) AS avg_v1
      ,avg(v2) AS avg_v2
      ,avg(v3) AS avg_v3
FROM   myTable
GROUP  BY 1;

Please direct me how to solve this issue. Thank you

GMB · Answer 1 · 2020-05-17T23:22:18.373

Consider casting the timestamp column to timestamp(1): this will actually round it to the closest decimal second.

SELECT 
    "timestamp"::timestamp(1) AS timestamp,
    avg(v1) AS avg_v1,
    avg(v2) AS avg_v2,
    avg(v3) AS avg_v3
FROM myTable
GROUP BY 1;

Note that this rounds the timestamp instead of truncating it. Typically, 08:10:20.306 would become 08:10:20.3, while 08:10:20.350 would be turned to 08:10:20.4. This might, or might not be what you really want.

If you really want to truncate to 10s of seconds, then it is a bit more complicated. One option is to truncate the timestamp to minutes, then extract the second parts (all decimals included), turn it to a number, truncate it to 1 decimal, then add it back:

SELECT 
    date_trunc('minute', "timestamp") 
        + interval '1 second' 
        * trunc((extract(seconds from  "timestamp"))::numeric, 1) AS timestamp,
    avg(v1) AS avg_v1,
    avg(v2) AS avg_v2,
    avg(v3) AS avg_v3
FROM myTable
GROUP BY 1;

Another option to truncate, as commented by Gordon Linoff, is to offset then round. This is simpler than the above solution, and probably more efficient:

SELECT 
    ("timestamp" - interval '50 millisecond')::timestamp(1) AS timestamp,
    avg(v1) AS avg_v1,
    avg(v2) AS avg_v2,
    avg(v3) AS avg_v3
FROM myTable
GROUP BY 1;

. . If you want to truncate to a tenth of a second (what the OP appears to be looking for), then you can just subtract 50 milliseconds: `("timestamp" - interval '50 millisecond')::timestamp(1)`. — Gordon Linoff, May 17 '20 at 23:19
@GordonLinoff: ah yes, of course, that's much simpler. Many thanks. — GMB, May 17 '20 at 23:22

Downsample Timestamp in milisecond range in Postgres

1 Answers1