Question 114831 in PostgreSQL Question posted in
The official documentation can be found here.

Rewrite PostgreSQL query using CTE:

I have the following code to pull records from a daterange in PostgreSQL, it works as intended. The "end date" is determined by the "date" column from the last record, and the "start date" is calculated by subtracting a 7-day interval from the "end date".

SELECT date 
FROM files
WHERE daterange((
    (SELECT date FROM files ORDER BY date DESC LIMIT 1) - interval '7 day')::date, -- "start date"
    (SELECT date FROM files ORDER BY date DESC LIMIT 1)::date, -- "end date"
    '(]') @> date::date 
ORDER BY date ASC

I’m trying to rewrite this query using CTEs, so I can replace those subqueries with values such as end_date and start_date. Is this possible using this method or should I look for other alternatives like variables? I’m still learning SQL.

WITH end_date AS 
(
    SELECT date FROM files ORDER BY date DESC LIMIT 1
), 
start_date AS 
(
    SELECT date FROM end_date - INTERVAL '7 day'
) 
SELECT date 
FROM files
WHERE daterange(
    start_date::date, 
    end_date::date, 
    '(]') @> date::date 
ORDER BY date ASC

Right now I’m getting the following error:

ERROR:  syntax error at or near "-"
LINE 7:     SELECT date FROM end_date - INTERVAL '7 day'
Tags:

3

Answers


  1. 0

    I think this is what you want:

    WITH end_date AS 
(
    SELECT date FROM files ORDER BY date DESC LIMIT 1
), 
start_date AS 
(
    SELECT date  - INTERVAL '7 day' as date
    FROM end_date
) 
SELECT F.date, S.date startDate, E.date endDate
FROM files F
JOIN start_date S on F.date >= S.date
JOIN end_date E on F.date <= E.date
ORDER BY date ASC;
    Login or Signup to reply.
  2. 0

    You do not need two CTEs, it’s one just fine, which can be joined to filter data.

    WITH RECURSIVE files AS (
    SELECT CURRENT_DATE date, 1 some_value
    UNION ALL
    SELECT (date + interval '1 day')::date, some_value + 1 FROM files
    WHERE date < (CURRENT_DATE + interval '1 month')::date
),
dates AS (
    SELECT 
        (MAX(date) - interval '7 day')::date from_date,
        MAX(date) to_date 
    FROM files    
)
SELECT f.* FROM files f
JOIN dates d ON daterange(d.from_date, d.to_date, '(]') @> f.date

    You even can make it to be a daterange initially in CTE and use it later like this

    WITH dates AS (
    SELECT 
        daterange((MAX(date) - interval '7 day')::date, MAX(date), '(]') range
    FROM files    
)
SELECT f.* FROM files f
JOIN dates d ON d.range @> f.date

    Here the first CTE is used just to generate some data.

    It will get all file lines for dates in the last week, excluding from_date and including to_date.

    date some_value
    2022-09-26 25
    2022-09-27 26
    2022-09-28 27
    2022-09-29 28
    2022-09-30 29
    2022-10-01 30
    2022-10-02 31
    Login or Signup to reply.
  3. 0

    I hope I’m not repeating anything, but if I understand your problem correctly I think this will work:

    with cte as (
  select max (date)::date as max_date from files
)
select date
from files
cross join cte
where date >= max_date - 7

    Or perhaps even:

    select date
from files
where date >= (select max (date)::date - 7 from files)

    Since you have already determined that the CTE has the max date, there is really no need to further bound it with a between, <= or range. You can simply say anything after that date minus 7 days.

    The error in your code above is because you want this:

    SELECT date - INTERVAL '7 day' as date FROM end_date

    And not this:

    SELECT date FROM end_date - INTERVAL '7 day'

    You are subtracting from the table, which doesn’t make sense.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search