PGSQL query to get a list of sequential dates from today-CodePudding

I am having an calendar table where I have added the list of dates on which no action should be performed. The table is as follows and the date format is YYYY-MM-DD

Considering today is 2021-11-24. The expected output is

date
2021-11-24
2021-11-23
2021-11-22

And Considering today is 2021-12-25 then the expected output is

date
2021-12-25

And Considering today is 2021-12-27 then the output should contain no data.

date

It should get me the sequence with today's date in descending order without a break of sequence. I searched on various posts I did find some of the posts related to my question but the query was little complex with nested subqueries. Is there a way to achieve the output in a more optimized way. I am new to pgsql.

CodePudding user response：

Using your example, I don't understand it as 2021-11-25 is greater than 2021-11-24. Also 2021-01-01 is less than the current date today but you haven't included it as 2021-04-05, 2021-04-05, 2021-04-06, 2021-04-07, 2021-08-10 and 2021-11-22...

CodePudding user response：

Create example table:

CREATE TABLE calendar (d date); 
INSERT INTO calendar VALUES ('2021-11-23'),('2021-11-20');

Query:

SELECT * FROM 
(SELECT CURRENT_DATE - '1 day'::interval * generate_series(0,10) AS d) a
LEFT JOIN calendar c ON (c.d=a.d);

          a.d        |  c.d
--------------------- ------------
 2021-11-14 00:00:00 | Null
 2021-11-15 00:00:00 | Null
 2021-11-16 00:00:00 | Null
 2021-11-17 00:00:00 | Null
 2021-11-18 00:00:00 | Null
 2021-11-19 00:00:00 | Null
 2021-11-20 00:00:00 | 2021-11-20
 2021-11-21 00:00:00 | Null
 2021-11-22 00:00:00 | Null
 2021-11-23 00:00:00 | 2021-11-23
 2021-11-24 00:00:00 | Null

Subquery "a" generates a date series, and then we join it to the table.

You can add conditions , for example "WHERE calendar.d IS NULL", or "IS NOT NULL" depending on the filtering you want.

CodePudding user response：

You can simply filter by an date range, building it by subtracting 2 days from today:

select  "date" 
from    maintenance_dates_70099898 
where   "date" <= now()::date --you want to see today and 2 days prior; Last 3 days total
and     "date" >= now()::date - '2 days'::interval
order by 1 desc;

With a runnable test:

drop table if exists maintenance_dates_70099898;
create table maintenance_dates_70099898 ("date" date);
insert into maintenance_dates_70099898 
    ("date")
values 
    ('2021-01-01'),
    ('2021-04-05'),
    ('2021-04-06'),
    ('2021-04-07'),
    ('2021-08-10'),
    ('2021-11-22'),
    ('2021-11-23'),
    ('2021-11-24'),
    ('2021-12-25'),
    ('2021-12-31');

select  "date" 
from    maintenance_dates_70099898 
where   "date" <= now()::date --you want to see today and 2 days prior; Last 3 days total
and     "date" >= now()::date - '2 days'::interval
order by 1 desc;
--    date
--------------
-- 2021-11-24
-- 2021-11-23
-- 2021-11-22
--(3 rows)

select  "date" 
from    maintenance_dates_70099898 
where   "date" >= '2021-12-25'::date - '2 days'::interval 
and     "date" <= '2021-12-25'::date
order by 1 desc;
--    date
--------------
-- 2021-12-25
--(1 row)

I assume that for 2021-12-27 you do want to see 2021-12-25, as it's within the 3 day range prior.

select  "date" 
from    maintenance_dates_70099898 
where   "date" >= '2021-12-28'::date - '2 days'::interval 
and     "date" <= '2021-12-28'::date
order by 1 desc;
-- date
--------
--(0 rows)