How to make an hours of week dimension table

16 Apr 2021

select row_number() over ()                 as id,
       the_time::time                       as start_time,
       the_time::time + interval '1 hour'   as end_time,
       extract(hour from the_time)          as hour,
       to_char(the_time, 'Day')             as day_str,
       extract(dow from the_time)           as day_int,
       to_char(the_time, 'am')              as am_pm
  from generate_series(timestamp '2021-01-31',
                       timestamp '2021-02-06 23:00:00',
                       interval '1 hour') as t(the_time);

Yields

┌─────┬────────────┬──────────┬──────┬───────────┬─────────┬───────┐
│ id  │ start_time │ end_time │ hour │  day_str  │ day_int │ am_pm │
├─────┼────────────┼──────────┼──────┼───────────┼─────────┼───────┤
│   1 │ 00:00:00   │ 01:00:00 │    0 │ Sunday    │       0 │ am    │
│   2 │ 01:00:00   │ 02:00:00 │    1 │ Sunday    │       0 │ am    │
│   3 │ 02:00:00   │ 03:00:00 │    2 │ Sunday    │       0 │ am    │
│   4 │ 03:00:00   │ 04:00:00 │    3 │ Sunday    │       0 │ am    │
│   5 │ 04:00:00   │ 05:00:00 │    4 │ Sunday    │       0 │ am    │
...
│ 165 │ 20:00:00   │ 21:00:00 │   20 │ Saturday  │       6 │ pm    │
│ 166 │ 21:00:00   │ 22:00:00 │   21 │ Saturday  │       6 │ pm    │
│ 167 │ 22:00:00   │ 23:00:00 │   22 │ Saturday  │       6 │ pm    │
│ 168 │ 23:00:00   │ 00:00:00 │   23 │ Saturday  │       6 │ pm    │
└─────┴────────────┴──────────┴──────┴───────────┴─────────┴───────┘
(168 rows)