First of all, the title of the article might be missleading, because in our case, we won't create a table, but rather a set of views that generate the time data. This logic can be enhanced and adapted to to suit any particular needs.
The process is quite simple: we have to generate the following .hdbview files (and the dummy table, if it does not exist):
/v_digits.hdbview
VIEW digits AS
SELECT 0 AS digit from dummy UNION ALL
SELECT 1 from dummy UNION ALL
SELECT 2 from dummy UNION ALL
SELECT 3 from dummy UNION ALL
SELECT 4 from dummy UNION ALL
SELECT 5 from dummy UNION ALL
SELECT 6 from dummy UNION ALL
SELECT 7 from dummy UNION ALL
SELECT 8 from dummy UNION ALL
SELECT 9 from dummy;
/v_numbers.hdbview
VIEW numbers AS
SELECT
ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
FROM
digits as ones,
digits as tens,
digits as hundreds,
digits as thousands;
/v_dates.hdbview
VIEW dates AS
SELECT
add_days(CURRENT_DATE, (number *-1)) AS date
FROM
numbers
UNION ALL
SELECT
add_days(CURRENT_DATE, (number + 1)) AS date
FROM
numbers;
-- if needed, also the dummy table:
/dummy.hdbtable
COLUMN TABLE dummy(
dummy boolean
)
COMMENT 'dummy table to get different hana variables. The table needs to be populated after the first deployment with the following script: "insert into dummy values(true);" '
After the views are deployed, you can simply get a series of dates as follows:
SELECT
date
FROM
dates
WHERE
date BETWEEN '2024-01-20' AND '2025-02-24'
ORDER BY
date;
