questdb

How to aggregate by 1 week interval in QuestDB?

I have a table with amount and timestamp columns, similar to Trades at https://demo.questdb.io. I want to aggregate the data to have 1 line per week with timestamp of Monday at 00:00 over some period time (say the year 2023), I tried a SAMPLE BY query like

select timestamp, sum(amount)
from trades
where timestamp in '2023'
sample by 7d

The result started from 2023-01-01T00:00:00.012980Z so I went to docs and added ALIGN TO CALENDAR

select timestamp, sum(amount)
from trades
where timestamp in '2023'
sample by 7d ALIGN TO CALENDAR

The result started from 2022-12-29T00:00:00.000000Z which is Thursday, not Monday. I tried to do SAMPLE BY 1w and 1week but it was rejected as invalid syntax.

How can I write a query to aggregate the data by 1 week starting Monday?

Solution

  • Weekly sample by unit seems to be missing in QuestDB. The workaround can be to group by timestamp truncated to the beginning of the week

    select date_trunc('week', timestamp) as timestamp, sum(amount)
from trades
where timestamp in '2023'
GROUP BY date_trunc('week', timestamp)

    QuestDB can add group by automatically, so it also can be written as

    select date_trunc('week', timestamp) as timestamp, sum(amount)
from trades
where timestamp in '2023'