Creating a PostgreSQL `tsrange` from two timestamps
WITH past_week AS ( SELECT date_trunc('day', NOW() + (s::TEXT || ' day')::INTERVAL)::TIMESTAMP(0) AS day FROM generate_series(-7, 0, 1) AS s)SELECT tsrange( date_trunc('day', (SELECT day FROM past_week WHERE EXTRACT(DOW FROM day) = '4') - '7 day'::INTERVAL), date_trunc('day', (SELECT day FROM past_week WHERE EXTRACT(DOW FROM day) = '4'))); tsrange ----------------------------------------------- ["2015-10-29 00:00:00","2015-11-05 00:00:00")(1 row)Using CURRENT_DATE your query may be as simple as:
WITH previous_thursday AS ( SELECT CURRENT_DATE- EXTRACT(DOW FROM CURRENT_DATE)::int+ 4 AS thursday )SELECT tsrange(thursday- '7d'::INTERVAL, thursday)FROM previous_thursday;