search

Seddryck / Tseesecake

Tseesecake is a lightweight proxy for time-series query engines, supporting multiple database engines and storage providers, with a SQL dialect dedicated to time series
https://seddryck.github.io/Tseesecake
Apache License 2.0
2 stars 1 forks source link

Time range definition in BUCKET BY clause #75

Open Seddryck opened 11 months ago

Seddryck commented 11 months ago

It should be possible to define the time range in the BUCKET BY clause. This clause is limiting the time range returned by the query but at the opposite of a filter clause (WHERE), it ensures that all timestamps in the range are effectively returned. E.g. if a timeseries starts on 2022-12-28 and the query is requesting to bucket by day over the range 2022-12-25 to 2022-12-31, the 7 rows with days between 2022-12-25 will be returned 2022-12-30 and the first 3 rows will have null value.

SELECT
    Instant,
    SUM(Forecasted) AS Forecasted
FROM
    WindEnergy
BUCKET BY
    DAY
    RANGE BETWEEN '2022-12-25' AND '2022-12-31'

Is returning the following table:

Instant Forecasted
2022-12-25 (null)
2022-12-26 (null)
2022-12-27 (null)
2022-12-28 450
2022-12-29 540
2022-12-30 670
2022-12-31 480

The values for the dates 2022-12-25 to 2022-12-27 are set to (null) because the timeseries has no value for these dates.