SQL get every hour and minute of day

Question

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 1

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

Answer 2

You can try below query having CONNECT BY clause as per your requirement -

WITH CTE(HOURS)AS
(
 SELECT LEVEL - 1
   FROM DUAL
 CONNECT BY LEVEL <= 24
),
MINUTES(MINUTES) AS
(
   SELECT LEVEL - 1
   FROM DUAL
 CONNECT BY LEVEL <= 60
)
SELECT X.HOURS,M.MINUTES
FROM CTE X
CROSS JOIN MINUTES M
ORDER BY X.HOURS,M.MINUTES;

Demo.

Answer 3

WITH CTE(HOURSS)AS
(
 SELECT 0 AS HOURSS
   UNION ALL
 SELECT C.HOURSS+1
 FROM CTE AS C
 WHERE C.HOURSS+1<=23
),
MINUTESS(MINUTESS) AS
(
  SELECT 0 AS MINUTESS
    UNION ALL
  SELECT MINUTESS+1
   FROM MINUTESS 
  WHERE MINUTESS+1<=59
)
SELECT X.HOURSS,M.MINUTESS
FROM CTE AS X
CROSS JOIN MINUTESS M
ORDER BY X.HOURSS,M.MINUTESS

Unfortunately, I do not have access to Oracle, so can not provide you with complete solution,but hope, this idea will help you (at least, you need to add FROM DUAL tothe appropriate parts of the query)