Get black friday dates in Postgres-CodePudding

How to get black friday dates in Postgres ? IT will be the last friday of November month, is it possible to get it somehow ?

CodePudding user response：

Use MAX() to get the last Friday in November:

    SELECT  MAX(d)::date AS black_friday
    FROM    generate_series('2000-01-01'::timestamp, '2051-01-01'::timestamp, INTERVAL '1 day') g(d)
    WHERE   EXTRACT(month from d) = 11 -- November
    AND     EXTRACT(dow FROM d) = 5 -- Friday
    AND     EXTRACT(DAY FROM d) BETWEEN 23 AND 29
    GROUP BY
        EXTRACT(year FROM d)
    ORDER BY 1;

CodePudding user response：

Building upon the answer from Frank Heikens (much better than my first answer, now deleted) with the corrected Black Friday definition from @Damien_The_Unbeliever

select  ("November Thursdays" '1 day'::interval)::date as "Black Fridays"
from
(   SELECT  "date"::date "November Thursdays",
            row_number() over (partition by EXTRACT(year FROM "date") order by "date")
    FROM    generate_series('2000-01-01'::date, 
                            '2051-01-01'::date, 
                            INTERVAL '1 day') dates("date")
    WHERE   EXTRACT(month from "date") = 11 -- November
    AND     EXTRACT(dow FROM "date") = 4 -- Thursday
    ORDER BY 1) Thursdays
where row_number=4;