CODEWITHSUNDEEP
sqloracle-database
Esperanza
Esperanza

Reputation: 115

Automatically populate a "time_hour" table

I have a requirement to store 24 hours in a day in a table "time_hours". Here is the structure of my table:

Create table time_hours
    (HOURS number,
    HOUR_RANGE VARCHAR (20),
    HOUR_MIN VARCHAR (20),
    HOUR_MAX VARCHAR(20));

Here is the script I have so far:

Insert into time_hours(HOURS,
   HOUR_RANGE,
   HOUR_MIN,
   HOUR_MAX)
Select 
to_number(to_char(t,'HH24'),'00'),
to_char(t,'HH24:MI:SS'),
to_char(t,'HH24:MI:SS'),
to_char(t,'HH24:MI:SS')
FROM
    (
    Select trunc(sysdate) + (level-1)/24 as t
    FROM dual
    Connect by level <=24
    );

The results is:

Hours | HOUR_RANGE | HOUR_MIN | HOUR_MAX
0       00:00:00    00:00:00   00:00:00  
1       01:00:00     01:00:00   01:00:00     
..    
23      23:00:00     23:00:00   23:00:00

However I need this output:

Hours | HOUR_RANGE | HOUR_MIN | HOUR_MAX
0       00h-00h59    00:00:00   00:59:59
1       01h00-01h59  01:00:00   01:59:59
2       02h00-02h59  02:00:00   02:59:59
..      ..           ..         .. 
23      23h00-23h59  23:00:00   23.59.59

My Question: How can I format the "HOUR_RANGE" and "HOUR_MAX" columns to give me the expected output?

Thank you all in advance for your help

Upvotes: 2

Views: 71

Answers (3)

Selim Nuhoğlu
Selim Nuhoğlu

Reputation: 9

I think you can use code below.

You can use pipelines to concatenate characters.

You can use some arithmetic operations to get max minutes and seconds of given hour; 1/24*60 = 1/1440 to add a minute to a date value. 1/24*60*60 = 1/86400 to add a second to a date value.

INSERT INTO time_hours (HOURS, HOUR_RANGE, HOUR_MIN, HOUR_MAX)
  SELECT
    to_number(to_char(t, 'HH24'), '00'),
    to_char(t, 'HH24') || 'h' || to_char(t, 'MI') || '-' || to_char(t, 'HH24') || 'h59',
    to_char(t, 'HH24:MI:SS'),
    to_char(t + 1/86400*59 + 1/1440*59, 'HH24:MI:SS')
  FROM (SELECT trunc(SYSDATE) + (LEVEL - 1) / 24 AS t
        FROM dual
        CONNECT BY LEVEL <= 24);

Query Result

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

After all, it's just strings containing the hour plus some other characters. No date conversion needed at all.

insert into time_hours(hours, hour_range, hour_min, hour_max)
select 
  level-1 as hours,
  to_char(level-1, '00') || 'h00-' || to_char(level-1, '00') || 'h59' as hour_range,
  to_char(level-1, '00') || ':00:00'as hour_min,
  to_char(level-1, '00') || ':59:59'as hour_max
from dual connect by level <= 24;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use:

Select 
  to_number(to_char(t,'HH24'),'00') AS Hours,
  to_char(t,'HH24"h"MI"-"HH24"h59"') AS HOUR_RANGE,
  to_char(t,'HH24:MI:SS') AS HOUR_MIN,
  to_char(t+1/24-1/(24*3600),'HH24:MI:SS') AS HOUR_MAX
FROM (Select trunc(sysdate) + (level-1)/24 as t
    FROM dual
    Connect by level <=24 );

Rextester Demo

Upvotes: 2

Related Questions