Hi,
I wonder if something like this would work for you?
Create a view with a structure like the TD calendar:
Code: Select all
dbadmin=> CREATE OR REPLACE VIEW calendar AS
dbadmin-> SELECT ts::DATE calendar_date,
dbadmin-> year(ts::DATE) year_of_calendar,
dbadmin-> month(ts::DATE) month_of_year,
dbadmin-> dayofweek(ts::DATE) day_of_week
dbadmin-> FROM (SELECT '01-01-0001'::TIMESTAMP as tm
dbadmin(> UNION
dbadmin(> SELECT '12-31-2500'::TIMESTAMP as tm) as t
dbadmin-> TIMESERIES ts as '1 Day' OVER (ORDER BY tm);
CREATE VIEW
You can play with the min and max dates for the calendar... I used 01/01/0001 and 12/31/2500.
Now run your select against the view:
Code: Select all
dbadmin=> SELECT calendar_date,
dbadmin-> year_of_calendar
dbadmin-> FROM (SELECT calendar_date,
dbadmin(> year_of_calendar,
dbadmin(> row_number() over (partition by year_of_calendar,month_of_year ORDER BY calendar_date ASC) rn
dbadmin(> FROM calendar
dbadmin(> WHERE month_of_year = 3
dbadmin(> AND year_of_calendar > 2010
dbadmin(> AND day_of_week = 1) foo
dbadmin-> WHERE rn = 2
dbadmin-> ORDER BY calendar_date
dbadmin-> LIMIT 10;
calendar_date | year_of_calendar
---------------+------------------
2011-03-13 | 2011
2012-03-11 | 2012
2013-03-10 | 2013
2014-03-09 | 2014
2015-03-08 | 2015
2016-03-13 | 2016
2017-03-12 | 2017
2018-03-11 | 2018
2019-03-10 | 2019
2020-03-08 | 2020
(10 rows)
Note: Thanks to sKwa for the great example of how to get date ranges!