generate_series for every hour in hh24:mi:ss forma

问题:

i have a generate series of every minute for the whole 24 hours. how about a generate _series showing every hour only in hh24:mi:ss and hh24:mi format.

result should be

00:00 01:00 02:00 : : 23:00

回答1:

This query:

SELECT to_char(generate_series(0, 23) * interval '1 hour' + '2019-01-01 00:00:00'::timestamp, 'HH24:MI')

will return:

00:00
01:00
02:00
03:00
...
23:00

Just modify the format string for what you desire.