This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 20, 2013 5:54 AM by user10274093

# diff date

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

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

#### Legend

• Correct Answers - 10 points