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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
sdstuberCommented:
change this portion...

:filtration_end - :filtration_start


to

trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')

Will this give 6 minutes and 8 seconds.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.