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'

CodePudding user response：

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;

CodePudding user response：

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.