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

# diff date

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
``(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
Thanks.

And how to have a total of duration for all rows ?
• ###### 3. Re: diff date
SUM it..
• ###### 4. Re: diff date
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
For this to work, the ranges have to be continuous, right?
• ###### 6. Re: diff date
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
Thanks.
But I need to have it in the same query.
Duration for each row and finally total duration.
Thanks.
• ###### 8. Re: diff date
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
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
Thank you and I mean,
``  the sum of all the durations determined for each row``
regards.
• ###### 11. Re: diff date
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..
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
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
``````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