[SOLVED] Filter data in QuestDB by number of week

Filter data in QuestDB by number of week

I want to query data by the week number. I know I can SAMPLE BY 1w, but I don't want to aggregate, just to filter rows, something like

SELECT * FROM trades
WHERE timestamp IN '2025-W21';

Solution

There is a built-in week_of_year() function, so this could be solved as

SELECT * FROM trades
WHERE week_of_year(timestamp) =24

However, depending on your table size, specially if you are not filtering by any timestamp, you might prefer this alternative, as it executes faster.

select * from trades where timestamp >= dateadd('w', 23, '2024-12-30') 
  AND timestamp < dateadd('w', 24, '2024-12-30');

You need to be careful with that query, as it will start counting time from Jan 1st 1970, which is not a monday. This alternative would start at the Monday of the week that includes January 1st.

DECLARE 
  @year := '2025',
  @week := 24,
  @first_monday := dateadd('d', -1 * day_of_week(@year) + 1,@year),   
  @week_start := dateadd('w', @week-1, @first_monday), 
  @week_end := dateadd('w', @week, @first_monday)
SELECT * from trades where timestamp >= @week_start and timestamp < @week_end;