CODEWITHSUNDEEP
sqlpostgresqltimescaledb
babbyldockr
babbyldockr

Reputation: 45

Howto limit timescaleDB queries to the used bucket size?

I have a postgres timescaleDB database with time series data. The data in table flows was sampled roughly every 500ms.

I need to get the data for every 1 second. I tried to do it with time_bucket() function.

This was my test query:

    SELECT time_bucket('1 second', time) AS bucket, value AS val 
    FROM flows fl 
    WHERE
        fl.time > '2021-08-31 06:14:00+00' AND 
        fl.time <= '2021-08-31 06:18:00+00' AND 
        fl.sensor_id = 2 
    ORDER BY fl.time ASC;

The returned data looks as follows:

|bucket                  |val                  |
| ---------------------- | ------------------- |
| 2021-08-31 06:14:00+00 | 9.75071040883207    |
| 2021-08-31 06:14:00+00 | 10.008532745208633  |
| 2021-08-31 06:14:01+00 | 9.953632354528265   |
| 2021-08-31 06:14:01+00 | 9.833033340905137   |
| 2021-08-31 06:14:02+00 | 9.77205680132453    |
| 2021-08-31 06:14:02+00 | 10.197350449765523  |
| ...                    | ...                 |

As you can see, there are two rows for each bucket of one second. Values are coming from the samples that were collected every 500ms.

How to make sure there is only one value per bucket? (In my case: One value every second)

I also tried an aggregation function (avg) on value, but that did not change the result.

Upvotes: 0

Views: 446

Answers (1)

Miranda
Miranda

Reputation: 41

For time_bucket functions, in order to get the bucketing to work correctly, you will have to aggregate the value column in some way, and provide a group by statment. For example, something like this should correctly bucket the time,

    SELECT time_bucket('1 second', time) AS bucket, 
    sum(value) AS val 
    FROM flows fl 
    WHERE
        time_bucket('1 second', time) > '2021-08-31 06:14:00+00' AND 
        time_bucket('1 second', time) <= '2021-08-31 06:18:00+00' AND 
        fl.sensor_id = 2 
    GROUP BY bucket, sensor_id
    ORDER BY bucket ASC;

Hopefully this works for you!

disclosure: I am a part of the Timescale team 😊

Upvotes: 3

Related Questions