Calendar sql query and Generate Series

[igcosta]

Hello everyone!
I am trying to create a Calendar in SQL to later join it to my dataset.
After a little research on this forum, I found these two posts that suggested to use the generate_series function.

Create timeline chart

Hello, I have a dataset with fields like start_date and end_date which reflect when an asset is unavailable. I want to create a timeline chart where the x axis will have a calendar. Namely, it should start with a date, for e.g 2010-01-01 and enable user to scroll and display future days with 1 day interval. My goal is to show a red line parallel to x axis line for the dates between start_date and end_date and a green line otherwise. I wonder what’s the most efficient way to do this? I saw QS d…

Generate series

Hello, I wonder if QuickSight has a function that allows me to generate a series with constant steps. For example, in SQL, SELECT value FROM GENERATE_SERIES(1, 50, 5); The output is value 1 6 11 16 21 26 31 36 41 46. Power BI has such a function called generateseries to do the same thing. I wonder if QuickSight can do this as well. Thanks

However, the generate_series function does not work on Quicksight, as it says it’s not a recognized function.

Even trying a basic query like SELECT GENERATE_SERIES(1, 10, 2) does not generate a result.

I also tried to do it with the following code below but it also did not work.
WITH NumberSeries AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM NumberSeries
WHERE Number < 10
)
SELECT Number
FROM NumberSeries;

Is there any way to create a Calendar query or use a generate_series like function in Quicksight’s SQL environment?

[Koushik_Muthanna]

@igcosta ,

The error is from SQL Server ( Fix “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server ) .

PostgreSQL Test