Create date and hour columns from date range

Question

I want to create a temp table where I give it a starting and ending date (pricedate). Then the results are those dates in one column and the hour of each day in the other column.

It would look like this:

date       hour
------------------
1/17/19     1
1/17/19     2
1/17/19     3
   .        .
   .        .
1/17/19    24
1/18/19     1
1/18/19     2
1/18/19     3
   .        .
   .        .
   .        .

So it seems like I want to use generate_series for the hour. But not sure how to match that up with dates.

This is not getting it done for me:

WITH hrs AS (SELECT * FROM generate_series(1,24)),
pricedate AS (SELECT * FROM generate_series('2018-01-24', '2018-01-26', interval '1 day'))


SELECT pricedate, hrs
WHERE pricedate BETWEEN '2018-01-24' AND '2018-01-26'
ORDER BY pricedate, hour

Answer 1

Use a cross join:

WITH hrs AS (
       SELECT gs.hr
       FROM generate_series(1,24) gs(hr)
      ),
      pricedate AS (
       SELECT gs.priceate
       FROM generate_series('2018-01-24', '2018-01-26', interval '1 day') gs(pricedate)
      )
SELECT pricedate.pricedate, hrs.hr
FROM hrs CROSS JOIN pricedate
WHERE pricedate.pricedate BETWEEN '2018-01-24' AND '2018-01-26'
ORDER BY pricedate.pricedate, hrs.hour