OraFAQ Forum: SQL & PL/SQL » How to calculate time difference if not using date datatype

Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate time difference if not using date datatype

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to calculate time difference if not using date datatype [message #229204]

Fri, 06 April 2007 00:35

plau18
Messages: 3
Registered: April 2007

Junior Member

Hello,

I'm new to Oracle and SQL and am trying to find a way to calculate the time difference when the time stamp is not using date datatype. I have the following output from a query on a table:

JOBNAME S STRTDATE STRTTI ENDDATE ENDTIM
-------------------- - -------- ------ -------- ------
FIN_MON_SUN_00:00 F 20070406 000018 20070406 000024
FAP_MON-SUN_03:11 F 20070406 031130 20070406 031131
MMM_MON_SUN_14:05 F 20070406 140518 20070406 150038
MWM_PERIODIC A 20070406 001500 20070406 004501
MWM_PERIODIC A 20070406 001500 20070407 021502
PMR_FRI_23:59 F 20070406 235917 20070407 000341
SOM_MON-SUN_02:00 F 20070406 000218 20070406 000620

I would like the output to also contain another column with the calculated runtime duration of the jobs in HH24:MI:SS format, Something like the following:

JOBNAME S STRTDATE STRTTI ENDDATE ENDTIM DURATN
-------------------- - -------- ------ -------- ------ ------
FIN_MON_SUN_00:00 F 20070406 000018 20070406 000024 00:00:06
FAP_MON-SUN_03:11 F 20070406 031130 20070406 031231 00:01:01
MMM_MON_SUN_14:05 F 20070406 140518 20070406 151538 01:10:20
MWM_PERIODIC A 20070406 001500 20070406 004501 00:30:01
MWM_PERIODIC A 20070406 001500 20070407 021602 26:01:02
PMR_FRI_23:59 F 20070406 235917 20070407 000341 00:04:24
SOM_MON-SUN_02:00 F 20070406 000218 20070406 000620 00:04:02

Or if it would be easier, just in total # of seconds should do too...like the following:

JOBNAME S STRTDATE STRTTI ENDDATE ENDTIM DURATN
-------------------- - -------- ------ -------- ------ ------
FIN_MON_SUN_00:00 F 20070406 000018 20070406 000024 000006
FAP_MON-SUN_03:11 F 20070406 031130 20070406 031231 000061
MMM_MON_SUN_14:05 F 20070406 140518 20070406 151538 004220
MWM_PERIODIC A 20070406 001500 20070406 004501 001801
MWM_PERIODIC A 20070406 001500 20070407 021602 093662
PMR_FRI_23:59 F 20070406 235917 20070407 000341 000264
SOM_MON-SUN_02:00 F 20070406 000218 20070406 000620 000202

The datatype for all is varchar2. Is this possible to do?

Report message to a moderator

Re: How to calculate time difference if not using date datatype [message #229206 is a reply to message #229204]

Fri, 06 April 2007 00:46

plau18
Messages: 3
Registered: April 2007

Junior Member

Sorry. Guess I should've read the forum on how to post my question first. Hope this makes it easier to read.

I have the following output from a query on a table:

JOBNAME             S STRTDATE STRTTI ENDDATE  ENDTIM
------------------- - -------- ------ -------- ------
FIN_MON_SUN_00:00   F 20070406 000018 20070406 000024
FAP_MON-SUN_03:11   F 20070406 031130 20070406 031131
MMM_MON_SUN_14:05   F 20070406 140518 20070406 150038
MWM_PERIODIC        A 20070406 001500 20070406 004501
MWM_PERIODIC        A 20070406 001500 20070407 021502
PMR_FRI_23:59       F 20070406 235917 20070407 000341
SOM_MON-SUN_02:00   F 20070406 000218 20070406 000620

I would like the output to also contain another column with the calculated runtime duration of the jobs in HH24:MI:SS, Something like the following:

JOBNAME             S STRTDATE STRTTI ENDDATE  ENDTIM  DURATN
------------------- - -------- ------ -------- ------  ------
FIN_MON_SUN_00:00   F 20070406 000018 20070406 000024  00:00:06
FAP_MON-SUN_03:11   F 20070406 031130 20070406 031231  00:01:01
MMM_MON_SUN_14:05   F 20070406 140518 20070406 151538  01:10:20
MWM_PERIODIC 	    A 20070406 001500 20070406 004501  00:30:01
MWM_PERIODIC        A 20070406 001500 20070407 021602  26:01:02
PMR_FRI_23:59       F 20070406 235917 20070407 000341  00:04:24
SOM_MON-SUN_02:00   F 20070406 000218 20070406 000620  00:04:02

Or if it would be easier, just in total # of seconds should do too...

JOBNAME             S STRTDATE STRTTI ENDDATE  ENDTIM  DURATN
------------------- - -------- ------ -------- ------  ------
FIN_MON_SUN_00:00   F 20070406 000018 20070406 000024  000006
FAP_MON-SUN_03:11   F 20070406 031130 20070406 031231  000061
MMM_MON_SUN_14:05   F 20070406 140518 20070406 151538  004220
MWM_PERIODIC 	    A 20070406 001500 20070406 004501  001801
MWM_PERIODIC  	    A 20070406 001500 20070407 021602  093662
PMR_FRI_23:59       F 20070406 235917 20070407 000341  000264
SOM_MON-SUN_02:00   F 20070406 000218 20070406 000620  000202

The datatype for all is varchar2. Is this possible to do?

JOBNAME            S STRTDATE STRTTI ENDDATE  ENDTIM  DURATN
------------------ - -------- ------ -------- ------  ------
FIN_MON_SUN_00:00  F 20070406 000018 20070406 000024  00:00:06
FAP_MON-SUN_03:11  F 20070406 031130 20070406 031231  00:01:01
MMM_MON_SUN_14:05  F 20070406 140518 20070406 151538  01:10:20
MWM_PERIODIC       A 20070406 001500 20070406 004501  00:30:01
MWM_PERIODIC  	   A 20070406 001500 20070407 021602  26:01:02
PMR_FRI_23:59      F 20070406 235917 20070407 000341  00:04:24
SOM_MON-SUN_02:00  F 20070406 000218 20070406 000620  00:04:02

Thanks.

Report message to a moderator

Re: How to calculate time difference if not using date datatype [message #229219 is a reply to message #229206]

Fri, 06 April 2007 01:36

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Assuming fields are VARCHAR2:

select (to_date(enddate||endtime,'YYYYMMDDHH24MISS')
        -to_date(strtdate||strttime,'YYYYMMDDHH24MISS'))
       *86400 duration_in_seconds
from mytable
/

To format it in hours:minutes:seconds have a look at my answer to the thread Calulate the Eaxct Hours, Minutes second.

Regards
Michel

Report message to a moderator

Re: How to calculate time difference if not using date datatype [message #229247 is a reply to message #229219]

Fri, 06 April 2007 04:21

plau18
Messages: 3
Registered: April 2007

Junior Member

That's great. It worked wonders. Thanks for the quick response.

Report message to a moderator

Previous Topic:	Help on questions faced at interview
Next Topic:	next and pervious date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 08:03:12 CST 2024