1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle PL/SQL - Adding time

Discussion in 'SQL PL/SQL' started by ertweety, Oct 6, 2014.

  1. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I have data that looks like the following.

    ID Date Time Length Interval_Num
    1 10/11/2014 9:00 420 14

    Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30).
    I need the data to show each interval. For the above example it should only be 14 intervals.
    For the above example the solution should look like....

    ID Date Time Length
    1 10/11/2014 9:00 30
    1 10/11/2014 9:30 30
    1 10/11/2014 10:00 30
    1 10/11/2014 10:30 30
    ........for 14 intervals up until 16:00

    I am using Oracle so dateadd does not work.

    Help!!!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Try the following:

    Code (SQL):
    WITH sch AS
      (SELECT 1 id, '10/11/2014' dt, '9:00' tm, 420 ln, 14 interval_num
      FROM dual
      )
    SELECT
      id "Id",
      to_char(to_date(dt ||tm, 'dd/mm/yyyyhh24:mi') + (level-1)*ln/(interval_num*24*60), 'dd/mm/yyyy') "Date",
      to_char(to_date(dt ||tm, 'dd/mm/yyyyhh24:mi') + (level-1)*ln/(interval_num*24*60), 'hh24:mi') "Time",
      ln/interval_num "Length"
    FROM sch
      CONNECT BY level <= interval_num ;

    Output:
    ---------

    Id     DATE                 TIME        LENGTH
    1   10/11/2014  09:00   30
    1   10/11/2014  09:30   30
    1   10/11/2014  10:00   30
    1   10/11/2014  10:30   30
    1   10/11/2014  11:00   30
    1   10/11/2014  11:30   30
    1   10/11/2014  12:00   30
    1   10/11/2014  12:30   30
    1   10/11/2014  13:00   30
    1   10/11/2014  13:30   30
    1   10/11/2014  14:00   30
    1   10/11/2014  14:30   30
    1   10/11/2014  15:00   30
    1   10/11/2014  15:30   30

     
    Note:
    - Assuming your date format is 'dd/mm/yyyy' or you can put appropriate format
    - Last time displayed is 15:30 (and not 16:00) if you start with 9:00
     
  3. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thank you!