Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 5
  • 4
  • 2
1 Solution
 
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
 
anumosesAuthor Commented:
trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')

Will this give 6 minutes and 8 seconds.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now