SQL get every hour and minute of day

I would like to create a table with 2 columns – Hour and Minute. Table will store every combination of hour and minute in the day

Example:

A header	Another header
0	1
0	2
0	3
…	…
0	59
1	0
1	1
1	2
…	…
23	59

I would like to populate the table using Oracle SQL. I can do that in C#, but I would like to have this done using SQL query. I know I have to use LEVEL CONNECT BY, but my knowledge of this is limited.

Anybody dealt with something similar?

Answer

You can use a simple hierarchical query and intervals:

SELECT EXTRACT(HOUR   FROM (LEVEL - 1) * INTERVAL '1' MINUTE) AS hour,
       EXTRACT(MINUTE FROM (LEVEL - 1) * INTERVAL '1' MINUTE) AS minute
FROM   DUAL
CONNECT BY LEVEL * INTERVAL '1' MINUTE <= INTERVAL '1' DAY;

or via calculations:

SELECT TRUNC((LEVEL - 1)/60) AS hours,
       MOD(LEVEL - 1, 60) AS minutes
FROM   DUAL
CONNECT BY LEVEL <= 24 * 60;

Which both output:

HOUR	MINUTE
0	0
0	1
0	2
0	3
…	…
23	57
23	58
23	59

db<>fiddle here