Re: Getting time difference between two columns in seconds?

On Jul 24, 11:08 am, Andre-John Mas <andrejohn...._at_gmail.com> wrote:
> Hi,
>
> I am wanting to get the date range between two columns in my database
> in seconds. I have a column startTime and a column endTime and I want
> to find out how many seconds has elapsed between the two. Originally I
> had the following as part of my select statement:
>
> SYSTEM.CALCTIMEDIFF (rc.starttime, rc.endtime) AS duration
>
> This returns a value in the form 'DD HH:MM:ss'. I changed the code to
> be:
>
> round((rc.endtime - rc.starttime)*24*60*60) AS duration
>
> This works in most cases but is failing in some.
>
> 1st method: 00 00:32:45
> 2nd method: 1966
> result: 1 second off
>
> 1st method: 00 00:32:41
> 2nd method: 1961
> result: ok
>
> 1st method: 00 00:09:59
> 2nd method: 32999
> result: very wrong
>
> Is there a better approach to getting the time difference between two
> columns and having the results in seconds?
>
> Andre

Turns out that the SYSTEM.CALCTIMEDIFF (rc.starttime, rc.endtime) function was something written in-house, and that the result being return was incorrect. The code that we added:

   round((rc.endtime - rc.starttime)*24*60*60) AS duration

actually provides the right answer. The following also provides the right answer:

  (to_number(to_char(rc.endtime, 'SSSSS'))- to_number(to_char(rc.startTime, 'SSSSS'))) AS durationSeconds

though the first approach is probably less computationally intensive.

Andre Received on Tue Jul 24 2007 - 10:39:20 CDT