Trino: Create a dynamic list of dates to fill values

Question

I was looking for a way to dynamically create a list of dates to join a table against, so that missing dates would be filled in.

Something like:

SELECT dt.date_col
     , ot.*
 FROM date_table AS dt -- Table with all dates?
 LEFT JOIN orig_table AS ot
   ON ot.date_col = dt.date_col

Answer 1

I found an answer in a coworker's code, but didn't see it on SO.

SELECT date_col
  FROM
UNNEST(sequence(date '2021-01-01', current_date, INTERVAL '1' DAY)) t (date_col)

So, in my example above, this could be used like:

WITH date_table AS (
    SELECT date_col
      FROM
    UNNEST(sequence(date '2021-01-01', current_date, INTERVAL '1' DAY)) t (date_col)
)
SELECT dt.date_col
     , ot.*
  FROM date_table AS dt
  LEFT JOIN orig_table AS ot
    ON ot.date_col = dt.date_col