CODEWITHSUNDEEP
postgresqlrangecommon-table-expression
Jir
Jir

Reputation: 3145

Creating a PostgreSQL `tsrange` from two timestamps

I am trying to create a tsrange (last Thursday to the previous Thursday) in a postgresql query but I get cast errors.

This is what I have got so far (starting off from this SO question).

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 (
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')));

And this is the result (correct value, but not format, since it's not a range):

                      row                      
-----------------------------------------------
 ("2015-10-29 00:00:00","2015-11-05 00:00:00")
(1 row)

Now, there are 2 main things that bug me:

  1. If I try and add a ::tsrange right before the end of the query, the interpreter complains that:

    ERROR: cannot cast type record to tsrange LINE 6: ...ROM past_week WHERE EXTRACT(DOW FROM day) = '4')))::tsrange;

  2. I would love to avoid repetition, but I'm not that proficient in SQL to know how. Any improvement is more than welcome.

Upvotes: 10

Views: 16895

Answers (1)

klin
klin

Reputation: 121634

Use the tsrange() constructor:

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')));

You can look for the past Thursday instead of the past week to simplify the query:

WITH past_thursday AS (
    SELECT current_date- extract(DOW FROM current_date)::int- 3 AS thursday
    )
SELECT tsrange(thursday- interval '7d', thursday)
FROM past_thursday;

or, using a derived table:

SELECT tsrange(thursday- interval '7d', thursday)
FROM (
    SELECT current_date- extract(DOW FROM current_date)::int- 3 AS thursday
) past_thursday;

Test it in db<>fiddle.

Upvotes: 15

Related Questions