Generate date series with repeating dates

Question

How to generate a long date series that repeats each date 5 times, like below?

1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
etc.

Answer 1

Redshift is not Postgres. Does not support generate_series().

The poor man's default is a number table (starting with 1). Create it once per database. Like demonstrated here.

Then:

SELECT date '2018-1-1' + day.number - 1
FROM   number AS day
     , number AS repeat
WHERE  day.number    <= 10  -- number of days in date range
AND    repeat.number <= 5   -- number of repetitions
ORDER  BY day.number;

---

In Postgres, you can use generate_series() directly:

SELECT day::date
FROM   generate_series(timestamp '2018-1-1'  -- desired date range
                     , timestamp '2018-1-5'  -- preferably in ISO foramt
                     , interval '1 day') day
     , generate_series(1,5) repeat;          -- number of repetitions

That's a CROSS JOIN, generating a Carthesian product, so each date is repeated 5 times in the example. Output is type is date after the cast. Why timestamp arguments? See:

• Generating time series between two dates in PostgreSQL

To get your particular date format use to_char():

SELECT to_char(day, 'FMMM/FMDD/YYYY')
FROM   ...

This is ordered like your example by default, but that's an implementation detail. If you need to guarantee a particular order, add ORDER BY.