Presenting about Quarkus at the Java Meet Up

Quarkus is a framework designed for Kubernetes to improve the startup time and memory footprint of Java apps running in docker containers

Recursive CTE to List Years Without Gaps

If I have a requirement to list the years from a start year incrementing to the current year without any gap years, I can write the following simple SELECT statement within a CTE.

WITH FINANCIAL_YEARS AS (
    SELECT 2020 AS FINANCIAL_YEAR
    UNION SELECT 2021
    UNION SELECT 2022
    UNION SELECT 2023  
)

However its pretty easy to see how tedious this can be especially when trying to maintain it when a new year ticks over. I found this neat trick using recursive CTEs to avoid gaps in dates and tweaked it to deal with year increments:

WITH FINANCIAL_YEARS AS (
    SELECT '2020-01-01'::DATE AS YEAR_DATE
    UNION ALL
    SELECT DATEADD(YEAR, 1, YEAR_DATE) AS YEAR_DATE
    FROM FINANCIAL_YEARS
    WHERE YEAR_DATE < DATEADD(YEAR, -1, DATEADD(MONTH, 6, CURRENT_DATE())
)
SELECT DATE_PART(YEAR, YEAR_DATE) AS FINANCIAL_YEAR FROM FINANCIAL_YEARS

Its even simpler if you only want the year component or any other kind of numeric increment from a starting number:

WITH FINANCIAL_YEARS AS (
    SELECT 2020 AS FINANCIAL_YEAR
    UNION ALL
    SELECT FINANCIAL_YEAR + 1 AS FINANCIAL_YEAR
    FROM FINANCIAL_YEARS
    WHERE FINANCIAL_YEAR < YEAR(DATEADD(MONTH, 6, CURRENT_DATE()))
)
SELECT FINANCIAL_YEAR FROM FINANCIAL_YEARS