1 2 Previous Next 19 Replies Latest reply: Feb 28, 2013 4:28 PM by user650888 RSS

    question about dates

    user650888
      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
          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
            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
              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
                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
                  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
                    Thanks to Frank and Chris for responding
                    • 7. Re: question about dates
                      user650888
                      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
                        Chris,

                        what is the importance of 1/24/12 ? I could not follow that
                        • 9. Re: question about dates
                          chris227
                          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
                            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
                              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
                                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
                                  Can I achieve the result using any analytic function ? for faster performance ?
                                  • 14. Re: question about dates
                                    chris227
                                    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