1 2 Previous Next 15 Replies Latest reply: Feb 20, 2013 7:54 AM by user10274093 RSS

    diff date

    user10274093
      Hi,
      on 11g R2 (11.2.0.3)

      How can I force the result of startdate-enddate to be in minutes :
      select starttime, endtime,
            (endtime-starttime)  duration
             from psprcsrqst
      
      
      starttime                 endtime                   DURATION    
      ------------------------- ------------------------- ----------- 
      16/05/12 21:01:00,041000000 16/05/12 21:01:15,495000000 0 0:0:15.454 
      16/05/12 21:41:49,615000000 16/05/12 21:42:04,649000000 0 0:0:15.034 
      Thanks.
        • 1. Re: diff date
          jeneesh
          (endtime-starttime)*24*60
          Ah, your data types are timestamp, then
          (extract(day from (endtime-starttime))*24*60) + 
          (extract(hour from (endtime-starttime))*60) +  
          (extract(minute from (endtime-starttime))) duration
          Edited by: jeneesh on Feb 20, 2013 2:54 PM
          • 2. Re: diff date
            user10274093
            Thanks.



            And how to have a total of duration for all rows ?
            • 3. Re: diff date
              jeneesh
              SUM it..
              • 4. Re: diff date
                APC
                user10274093 wrote:
                And how to have a total of duration for all rows ?
                Like this:
                   select 
                      (max(endtime) - min(starttime) ) total_duration
                       from psprcsrqst
                Cheers, APC
                • 5. Re: diff date
                  jeneesh
                  For this to work, the ranges have to be continuous, right?
                  • 6. Re: diff date
                    BluShadow
                    jeneesh wrote:
                    For this to work, the ranges have to be continuous, right?
                    Depends what's meant by "total duration".

                    I would say APC's answer is working towards the "overall duration", as something that works it out from the minimum start to the maximum end would be the "overall duration".
                    "total" indicates a form of summing
                    "overall" indicates across all records

                    OP needs to be clear in requirements.
                    • 7. Re: diff date
                      user10274093
                      Thanks.
                      But I need to have it in the same query.
                      Duration for each row and finally total duration.
                      Thanks.
                      • 8. Re: diff date
                        BluShadow
                        user10274093 wrote:
                        Thanks.
                        But I need to have it in the same query.
                        Duration for each row and finally total duration.
                        Thanks.
                        Explain what you mean by "total duration". Do you mean the sum of all the durations determined for each row, or do you mean the overall duration from the lowest start time to the highest end time across all records?
                        • 9. Re: diff date
                          APC
                          user10274093 wrote:
                          Thanks.
                          But I need to have it in the same query.
                          Duration for each row and finally total duration.
                          There's any number of ways to do that, ROLLUP or whatever. The more important quetion is the one raised by Jeneesh: do you want total elapsed time or just the sum of all the time ranges? Because my query won't give you that. And we can't just SUM() intervals.

                          So what you need to do is soemthing like this:
                              with data as ( select endtime-starttime ela from your_table )     
                             select sum (
                                   extract(day from ela) * 86400 
                                    + extract(hour from  ela) * 3600 
                                    + extract(minute from ela) * 60 
                                    + extract(second from ela 
                                  )  total_ela
                            from data
                          Which will give you the total number of seconds. You can then convert them in days. hours, mins, seconds by reversing the arithmetical operations. Timestamps are accurate but otherwise a real PITN.

                          Cheers, APC
                          • 10. Re: diff date
                            user10274093
                            Thank you and I mean,
                              the sum of all the durations determined for each row
                            regards.
                            • 11. Re: diff date
                              APC
                              user10274093 wrote:
                              I mean, the sum of all the durations determined for each row
                              Then the calculation I gave you in my last answer is what you're looking for.

                              Cheers, APC
                              • 12. @OP: Please put SOME effort to understand what others are saying..
                                jeneesh
                                The most irritating thing is that - people are putting no effort to understand what others are saying..

                                Simply asking questions one by one.

                                If anyone questions this attitude, they will say - "I did not compel you to answer.. Why are you shouting....."

                                So, keeping quite is the best option..
                                • 13. Re: diff date
                                  user10274093
                                  Ok, Thank again.
                                  I do not see how to include the both queries :

                                  select starttime, endtime,
                                        (endtime-starttime)  duration
                                         from mytable
                                  
                                  
                                  with data as ( select endtime-starttime ela from your_table )     
                                     select sum (
                                           extract(day from ela) * 86400 
                                            + extract(hour from  ela) * 3600 
                                            + extract(minute from ela) * 60 
                                            + extract(second from ela 
                                          )  total_ela
                                    from data
                                  • 14. Re: diff date
                                    jeneesh
                                    with data as ( select starttime,endtime,endtime-starttime ela from your_table )     
                                       select starttime,endtime,sum (
                                             extract(day from ela) * 86400 
                                              + extract(hour from  ela) * 3600 
                                              + extract(minute from ela) * 60 
                                              + extract(second from ela 
                                            ) over() total_ela
                                      from data;
                                    1 2 Previous Next