Skip to content
Advertisement

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?

Advertisement

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

4 People found this is helpful
Advertisement