Populating Multi-Year Calendar Table using SQL

February 27, 2023

I want to populate a multi-year calendar table and calculate the value of multiple fields based on the date. I’m running this script on Postgres, it is taking forever to run even if I pass start_date as 2023-02-16 and end_date as 2023-02-17. Please help with what is wrong here.

>Solution :

Example using generate_series():

INSERT INTO t1(....)
SELECT 
    (g.d)::date AS date,
    EXTRACT(year FROM g.d) AS year,
    EXTRACT(month FROM g.d) AS month,
    EXTRACT(day FROM g.d) AS day,
    EXTRACT(quarter FROM g.d) AS quarter,
    EXTRACT(week FROM g.d) AS week,
    EXTRACT(isodow FROM g.d) AS isodow,
    EXTRACT(doy FROM g.d) AS doy,
    EXTRACT(isoyear FROM g.d) AS isoyear
   FROM generate_series('1970-01-01 00:00:00'::timestamp without time zone, '2049-12-31 00:00:00'::timestamp without time zone, '1 day'::interval) g(d)
 ORDER BY g.d;

Just change the table name t1 and the columns you need. It take 0.1 second on my laptop to generate and insert 29220 dates.