oracle date- total elapsed time calculation

http://www.experts-exchange.com/Database/Oracle/Q_27543396.html
Already answered.

      :total_elapsed_time :=

select to_char(to_date('00:00:00','HH24:MI:SS')
 + (:filtration_end - :filtration_start), 'HH24:MI');                  
 
start 3/28/2012 9:13:52 AM
 
 end 3/28/2012 9:20:00 AM
Its giving me 00:06
but its seven minutes. Need help in fixing this
LVL 6
anumosesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
change this portion...

:filtration_end - :filtration_start


to

trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')
0
 
sdstuberCommented:
It's neither 6 minutes nor 7 minutes.

it's 6 minutes and 8 seconds.

however.  your format  HH24:MI  means you are only displaying the minutes and are dropping the seconds
0
 
anumosesAuthor Commented:
trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')

Will this give 6 minutes and 8 seconds.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
no

for one, even if the calculation did preserve the seconds, the HH24:MI format in the TO_CHAR didn't change, and that's why you don't see the seconds.

second, truncation removes the seconds so the math is at the minute level only

third - just try it,  always test for yourself.
0
 
slightwv (䄆 Netminder) Commented:
Do you want 6 minutes and 8 seconds?  In the other question you said you did not want seconds.

If you do just add seconds to the format:

select to_char(to_date('00:00:00','HH24:MI:SS')
 + (:filtration_end - :filtration_start), 'HH24:MI:SS');

If you do not want the seconds and want it rounded up then sdstuber's post will work.
0
 
anumosesAuthor Commented:
I want minutes and seconds
0
 
sdstuberCommented:
>>> I want minutes and seconds


then why did you ask for 7 minutes?


when you change the requirements like that -you completely invalidate previous effort and turn correct answers into wrong answers.
0
 
slightwv (䄆 Netminder) Commented:
>>I want minutes and seconds

That is not what you asked for initially...

anyway:  http:#a37777827
0
 
anumosesAuthor Commented:
Its my mistake. it was gicing 00:06 So I said wanted 7 minutes.
0
 
sdstuberCommented:
I suggest continuing with this question as originally asked
and then opening a new question to include the seconds.

that way the original posts, which apply to the original question will still be valid,  and slightwv can repost in the other question with his suggestion for the new requirement
0
 
anumosesAuthor Commented:
Will post another one for slightvw to answer
0
All Courses

From novice to tech pro — start learning today.