Calendar Basic

-- Use or create database (adjust as needed)
-- \c test;

DROP TABLE IF EXISTS calendar_basic;

CREATE TABLE calendar_basic (
    date_id INT NOT NULL PRIMARY KEY,
    full_date DATE NOT NULL,
    date_name VARCHAR(10) NOT NULL,
    year_month INT,
    year_week INT,
    yqmd VARCHAR(10) NOT NULL,
    day_of_week INT NOT NULL,
    day_of_week_name VARCHAR(10) NOT NULL,
    day_of_month INT NOT NULL,
    month_name VARCHAR(10) NOT NULL,
    month_abbr VARCHAR(3) NOT NULL,
    day_of_year INT NOT NULL,
    is_week_day BOOLEAN NOT NULL,
    is_weekend_day BOOLEAN NOT NULL,
    is_holiday BOOLEAN NOT NULL,
    week_of_year INT NOT NULL,
    month_of_year INT NOT NULL,
    is_last_day_of_month BOOLEAN NOT NULL,
    calendar_quarter INT NOT NULL,
    calendar_semester INT NOT NULL,
    calendar_year INT NOT NULL,
    fiscal_month_of_year INT NOT NULL,
    fiscal_quarter INT NOT NULL,
    fiscal_semester INT NOT NULL,
    fiscal_year INT NOT NULL
);

DO $$
DECLARE
    process_date DATE;
BEGIN
    process_date := '2000-01-01'::DATE;
    
    WHILE process_date <= '2030-12-31'::DATE LOOP
        INSERT INTO calendar_basic (
            date_id,
            full_date,
            date_name,
            year_month,
            year_week,
            yqmd,
            day_of_week,
            day_of_week_name,
            day_of_month,
            month_name,
            month_abbr,
            day_of_year,
            is_week_day,
            is_weekend_day,
            is_holiday,
            week_of_year,
            month_of_year,
            is_last_day_of_month,
            calendar_quarter,
            calendar_semester,
            calendar_year,
            fiscal_month_of_year,
            fiscal_quarter,
            fiscal_semester,
            fiscal_year
        )
        SELECT 
            TO_CHAR(process_date, 'YYYYMMDD')::INT AS date_id,
            process_date AS full_date,
            TO_CHAR(process_date, 'YYYY-MM-DD') AS date_name,
            TO_CHAR(process_date, 'YYYYMM')::INT AS year_month,
            (TO_CHAR(process_date, 'YYYY') || LPAD(EXTRACT(WEEK FROM process_date)::TEXT, 2, '0'))::INT AS year_week,
            (TO_CHAR(process_date, 'YYYY') || '0' || EXTRACT(QUARTER FROM process_date)::TEXT || 
             LPAD(EXTRACT(MONTH FROM process_date)::TEXT, 2, '0') || 
             LPAD(EXTRACT(DAY FROM process_date)::TEXT, 2, '0'))::VARCHAR(10) AS yqmd,
            EXTRACT(ISODOW FROM process_date)::INT AS day_of_week,
            TO_CHAR(process_date, 'Day')::VARCHAR(10) AS day_of_week_name,
            EXTRACT(DAY FROM process_date)::INT AS day_of_month,
            TO_CHAR(process_date, 'Month')::VARCHAR(10) AS month_name,
            TO_CHAR(process_date, 'Mon')::VARCHAR(3) AS month_abbr,
            EXTRACT(DOY FROM process_date)::INT AS day_of_year,
            CASE 
                WHEN EXTRACT(ISODOW FROM process_date) NOT IN (6, 7) THEN TRUE
                ELSE FALSE
            END AS is_week_day,
            CASE 
                WHEN EXTRACT(ISODOW FROM process_date) IN (6, 7) THEN TRUE
                ELSE FALSE
            END AS is_weekend_day,
            FALSE AS is_holiday,
            EXTRACT(WEEK FROM process_date)::INT AS week_of_year,
            EXTRACT(MONTH FROM process_date)::INT AS month_of_year,
            CASE 
                WHEN EXTRACT(MONTH FROM process_date) <> EXTRACT(MONTH FROM process_date + INTERVAL '1 day') THEN TRUE
                ELSE FALSE
            END AS is_last_day_of_month,
            EXTRACT(QUARTER FROM process_date)::INT AS calendar_quarter,
            CASE 
                WHEN EXTRACT(MONTH FROM process_date) < 7 THEN 1
                ELSE 2
            END AS calendar_semester,
            EXTRACT(YEAR FROM process_date)::INT AS calendar_year,
            EXTRACT(MONTH FROM process_date)::INT AS fiscal_month_of_year,
            EXTRACT(QUARTER FROM process_date)::INT AS fiscal_quarter,
            CASE 
                WHEN EXTRACT(MONTH FROM process_date) < 7 THEN 1
                ELSE 2
            END AS fiscal_semester,
            EXTRACT(YEAR FROM process_date)::INT AS fiscal_year;
        
        process_date := process_date + INTERVAL '1 day';
    END LOOP;
END $$;

-- Test query
SELECT *
FROM calendar_basic
WHERE date_id >= 20260101
ORDER BY date_id
LIMIT 1;