This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 28, 2013 2:28 PM by user650888 RSS

question about dates

user650888 Newbie
Currently Being Moderated
create table mytest(sd date, ed date, value number);
insert into mytest (SD, ED, VALUE)
values (to_date('01-01-2013 19:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-2013 19:05:00', 'dd-mm-yyyy hh24:mi:ss'), 45);

insert into mytest (SD, ED, VALUE)
values (to_date('01-01-2013 20:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-2013 20:15:00', 'dd-mm-yyyy hh24:mi:ss'), 30);

commit;
If the time difference between ED and SD is 15 minutes, I want to break that record into
three (5 minutes each and divide the value / 3 ) , and show it, bypassing the original record


I need a select that shows following
SD                                   ED                       VALUE
01-Jan-13 7:00:00 PM   01-Jan-13 7:05:00 PM        45

01-Jan-13 8:00:00 PM      01-Jan-13 8:05:00 PM         10

01-Jan-13 8:05:00 PM      01-Jan-13 8:10:00 PM         10

01-Jan-13 8:10:00 PM      01-Jan-13 8:15:00 PM      10
Notice the record that shows from 8:00 to 8:15 is gone and is replaced by last 3 records, with value equally divided (30/number of times, 3) and shown


I am struck here

select sd, ed,
case when to_char(ed,'MI') - TO_CHAR(sd,'MI') = 15 THEN
'Y'
END
FROM MYTEST

so using to_char we can extract the minute
  • 1. Re: question about dates
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    WITH     cntr     AS
    (
         SELECT     LEVEL - 1     AS n
         FROM     dual
         CONNECT BY     LEVEL     <= 3
    )
    SELECT       NVL2 ( c.n
                , m.sd + ( (5 * c.n) 
                             / (24 / 60)
                         )
                , m.sd 
                )          AS sd
    ,       NVL2 ( c.n
                , m.ed + ( (5 * (2 - c.n))
                             / (24 / 60)
                         )
                , m.ed 
                )          AS ed
    ,       NVL2 ( c.n
                , m.value / 3
                , m.value
                )          AS value
    FROM           mytest  m
    LEFT OUTER JOIN      cntr      c  ON  m.ed - m.sd = 15 / (24 * 60)
    ORDER BY  sd
    ;
    You could probably do something with UNION that requires less code, but it will probably be slower than this.

    Extracting the minutes from sd and ed won't help much. For example, if sd is 17:20 on Feb. 22, 2013, then the value of the minutes is 20. If the extracted minutes from ed is 35, does that mean ed is 15 minutes later than sd? Not necessarily. Ed could be 16:35, or 18:35, or it could be 17:35 on some other day.
  • 2. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    I tried to apply my logic, no luck so far


    select case when to_char(ed,'MI') - TO_CHAR(sd,'MI') = 15 THEN
    sd + 5/1440
    else
    sd
    END AS sd ,
    case when to_char(ed,'MI') - TO_CHAR(sd,'MI') = 15 THEN
    ED + 5/1440
    else
    ED
    END AS ed
    FROM MYTEST
  • 3. Re: question about dates
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user650888 wrote:
    I tried to apply my logic,
    Why? What's wrong with the query I posted? Point out where the results it produces are wrong, and explain how you get the right results in those places. If necessary, post new sample data and desired results.
    no luck so far


    select case when to_char(ed,'MI') - TO_CHAR(sd,'MI') = 15 THEN
    There are so many things wrong with that.
    (1) TO_CHAR, as the name implies, returns a <b>CHAR</b>acter string, such as '20'. You can't subtract strings.
    (2) If sd is in the last quarter of an hour (that is, between '45' and '59'), nothing will ever be '15' minutes later, because there are no times like '60' or '74'.
    (3) That only looks at minutes; it doesn't take into account hours or days. For example, sd could produce '20' and ed could produce '35', but that might be because ed is an hour and 15 minutes later than sd, or 2 hours and 15 minutes, or ed could be earlier than sd, or ed and sd could be on completely different days.
  • 4. Re: question about dates
    chris227 Guru
    Currently Being Moderated
    select
     to_char(d,'DD-MM-YYYY HH24:MI:SS') sd
    ,to_char(ed,'DD-MM-YYYY HH24:MI:SS') ed
    ,value
    from mytest
    model return updated rows
    partition by (myid,sd,ed eed)
    dimension by (Date '1970-01-01' d)
    measures (sd s, ed, value)
    rules(
     value[for d from s[Date '1970-01-01'] to ed[Date '1970-01-01']-1/(24*12) increment 1/(24*12)]=
     value[Date '1970-01-01']/(ed[Date '1970-01-01']-s[Date '1970-01-01'])/24/12
    ,ed[d > Date '1970-01-01']=cv(d)+1/(24*12)
    )
    
    SD     ED     VALUE
    01-01-2013 19:00:00     01-01-2013 19:05:00     45
    01-01-2013 20:00:00     01-01-2013 20:05:00     10
    01-01-2013 20:05:00     01-01-2013 20:10:00     10
    01-01-2013 20:10:00     01-01-2013 20:15:00     10
    Date '1970-01-01' is a arbitray date below your date-range in use solely to come to a unique dimension.

    Edited by: chris227 on 22.02.2013 15:25
    Used Date '1970-01-01' for readability

    Edited by: chris227 on 25.02.2013 13:17
  • 5. Re: question about dates
    chris227 Guru
    Currently Being Moderated
    Perhaps you like this one more
    select
     to_char(sd+(level-1)*1/24/12,'DD-MM-YYYY HH24:MI:SS') sd
    ,to_char(sd+level*1/24/12,'DD-MM-YYYY HH24:MI:SS') ed
    ,value / (ed-sd)/24/12 value
    from mytest
    connect by
    sd+level*1/24/12 <=ed
    and
    prior sd = sd
    and
    prior sys_guid() is not null
    
    SD     ED     VALUE
    01-01-2013 19:00:00     01-01-2013 19:05:00     45
    01-01-2013 20:00:00     01-01-2013 20:05:00     10
    01-01-2013 20:05:00     01-01-2013 20:10:00     10
    01-01-2013 20:10:00     01-01-2013 20:15:00     10
  • 6. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    Thanks to Frank and Chris for responding
  • 7. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    This solution does not work if I add a row for a different end date
    insert into mytest (SD, ED, VALUE)
    values (to_date('01-01-2013 20:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('02-01-2013 20:15:00', 'dd-mm-yyyy hh24:mi:ss'), 30);
     
    commit;
    The select gives me 2319 rows
  • 8. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    Chris,

    what is the importance of 1/24/12 ? I could not follow that
  • 9. Re: question about dates
    chris227 Guru
    Currently Being Moderated
    Your data model lacks of the existence of a "row-identifier"
    We may work around it like this using the combination of sd and ed as unique key (above i only used sd):
    select
     to_char(sd,'DD-MM-YYYY HH24:MI:SS') sd
    ,to_char(sd+level*1/24/12,'DD-MM-YYYY HH24:MI:SS') ed
    ,value / (ed-sd)/24/12
    from mytest
    connect by
    sd+level*1/24/12 <=ed
    and
    prior sd = sd
    and
    prior ed = ed
    and
    prior sys_guid() is not null
    1/24/12 are just 5 minutes: 1 day /24 hours / 12

    Edited by: chris227 on 25.02.2013 09:46
  • 10. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    I added a new column myid, and the primary key is the combination of myid, sd, ed, with the below set of data, Please advice how to get the correct results, I am trying to understand
    your query as I am working..
    drop table mytest ;
    
    create table mytest(myid number,sd date, ed date, value number);
    
    
    
    
    insert into mytest(myid,sd, ed, value) values (1,to_date('23-JAN-2013 23:50:00','DD-MON-YYYY HH24:MI:SS'),
                                              to_date('23-JAN-2013 23:55:00','DD-MON-YYYY HH24:MI:SS'),
                                              45);
                                              
    
    insert into mytest(myid,sd, ed, value) values (1,to_date('23-JAN-2013 23:55:00','DD-MON-YYYY HH24:MI:SS'),
                                              to_date('24-JAN-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),
                                              45);
                                              
    
    insert into mytest(myid,sd, ed, value) values (1,to_date('24-JAN-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),
                                              to_date('24-JAN-2013 00:15:00','DD-MON-YYYY HH24:MI:SS'),
                                              45);   
                                              
    -- for 2
    
    insert into mytest(myid,sd, ed, value) values (2,to_date('23-JAN-2013 23:50:00','DD-MON-YYYY HH24:MI:SS'),
                                              to_date('23-JAN-2013 23:55:00','DD-MON-YYYY HH24:MI:SS'),
                                              45);
                                              
    
    insert into mytest(myid,sd, ed, value) values (2,to_date('23-JAN-2013 23:55:00','DD-MON-YYYY HH24:MI:SS'),
                                              to_date('24-JAN-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),
                                              45);
                                              
    
    insert into mytest(myid,sd, ed, value) values (2,to_date('24-JAN-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),
                                              to_date('24-JAN-2013 00:15:00','DD-MON-YYYY HH24:MI:SS'),
                                              45);                                            
                                              
    
    
    alter table mytest add constraint pk_mytest primary key (myid,sd,ed);     
    Also I noticed (ed-sd)/24/12 is truncating (correctly) and taking either 1 (for level 1) or 3

    Example : 1.20563271604938E-5 is considered as 1

    and 3.61689814814815E-5 is considered as 3

    I wonder how that is happening...

    Can we do away with "prior sys_guid() is not null" now that I defined primary key ?
  • 11. Re: question about dates
    chris227 Guru
    Currently Being Moderated
    user650888 wrote:
    I added a new column myid, and the primary key is the combination of myid, sd, ed, with the below set of data, Please advice how to get the correct results, I am trying to understand
    your query as I am working..
    The new key changes nothing but one condition more
    select
     to_char(sd,'DD-MM-YYYY HH24:MI:SS') sd
    ,to_char(sd+level*1/24/12,'DD-MM-YYYY HH24:MI:SS') ed
    ,value / (ed-sd)/24/12
    from mytest
    connect by
    sd+level*1/24/12 <=ed
    and
    prior sd = sd
    and
    prior ed = ed
    and
    prior myid = myid
    and
    prior sys_guid() is not null
    The connect by generates rows for every distinct myid,sd,ed.
    sys_guid() is a needed "trick" to make the self-cycle work.
    How many rows is determined by sd+level*1/24/12 <=ed.
    Level in increasing from 1 to the count of 5-minutes fitting in ed-sd.

    The calculation of the value is the easiest part, since it is always the overall value divided by the number of 5-minute-chunks between sd and ed.

    Edited by: chris227 on 25.02.2013 12:12
  • 12. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    This may work, But when I tried the below on my original table (which is huge)

    select
     myid,
     LEVEL,
     sd+(level-1)*1/24/12 sd
    ,sd+level*1/24/12 ed
    ,value / (ed-sd)/24/12
    from mytest
    where myid = 1
    connect by
    sd+level*1/24/12 <=ed
    and
    prior sd = sd
    and
    prior ed = ed
    and
    prior myid = myid
    and
    prior sys_guid() is not null
    The query is taking a lot of time to get the result, (even for one particular id as I mentioned in the where above)

    I have a unique index on myid, sd and ed (because of primary key)

    And I removed to_char so the query makes use of index, But explain plan tells me TABLE ACCESS FULL
  • 13. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    Can I achieve the result using any analytic function ? for faster performance ?
  • 14. Re: question about dates
    chris227 Guru
    Currently Being Moderated
    user650888 wrote:
    Can I achieve the result using any analytic function ? for faster performance ?
    You want to generate new rows. How to achieve this with analytical functions?

    However you may try the model solution above as comparison.
    On 11.2 recursive with clause is also possible.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points