Oracle-L: Re: How to calculate time using SQL

From: John Carlson <jcarlson_at_CJ.COM>
Date: Mon, 01 Apr 2002 10:04:38 -0800
Message-ID: <F001.00438056.20020401100438@fatcity.com>

Here is a script I recently made to help me understand how the date worked.

>>>>>>>>>>>>>>
REM Elapsed Time

define start_time = '03-25-02 11:17:12'
define stop_time  = '03-27-02 12:36:30'
define date_fmt   = 'mm-dd-yy hh24:mi:ss'

select trunc(to_date('&&stop_time', '&&date_fmt')

to_date('&&start_time', '&&date_fmt')) "Elapsed: Days", trunc(mod((to_date('&&stop_time', '&&date_fmt')
to_date('&&start_time', '&&date_fmt')),1) * 24) "Hours", trunc(mod( 24 * (to_date('&&stop_time', '&&date_fmt')
to_date('&&start_time', '&&date_fmt')),1) * 60) "Minutes", mod( (24*60) * (to_date('&&stop_time', '&&date_fmt')
to_date('&&start_time', '&&date_fmt')),1) * 60 "Seconds" from dual /
- The following show how the above query is broken down.
- The mod or remainder of each calc is multipled to get time.

select (to_date('&&stop_time', '&&date_fmt') - to_date('&&start_time', '&&date_fmt')) "Elapsed Days" from dual
/

select (24) * (to_date('&&stop_time', '&&date_fmt') - to_date('&&start_time', '&&date_fmt')) "Elapsed Hours" from dual
/

select (60*24) * (to_date('&&stop_time', '&&date_fmt') - to_date('&&start_time', '&&date_fmt')) "Elapsed Minutes" from dual
/

select (60*60*24) * (to_date('&&stop_time', '&&date_fmt') - to_date('&&start_time', '&&date_fmt')) "Elapsed Seconds" from dual
/

<<<<<<

Run it and see how it works and then experiment yourself. One thing I haven't tried is 'sssss'. The book says it is 'seconds past midnight'. I guess if you had a job that never ran past midnight, you could just subtract the begining time from the end time and get seconds that way.

Of course, you can 'set timing on' but that is only good for each step. If you have a job that has several sql statements and you want to get the total time, you need something like what I have done.

HTH,
John

>>> david.m.nguyen_at_xo.com 04/01/02 07:48AM >>> Is it possible to calculate seconds using SQL?

For example, I'd like to subtract these two time to get difference in seconds:

10:20:32 - 10:25:29

Thanks,
David
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nguyen, David M
INET: david.m.nguyen_at_xo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: John Carlson
INET: jcarlson_at_CJ.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------