Filter Data by Week Number

Filter time-series data by week number using either the built-in week_of_year() function or dateadd() for better performance on large tables.

Solution 1: Using week_of_year()​

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

SELECT * FROM trades
WHERE week_of_year(timestamp) = 24;

Solution 2: Using dateadd() (Faster)​

However, depending on your table size, especially 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.

Solution 3: Start at First Monday of Year​

This alternative would start at the Monday of the week that includes January 1st:

Filter by week using first Monday calculationDemo this query
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;
Related Documentation