Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate time difference if not using date datatype
icon5.gif  How to calculate time difference if not using date datatype [message #229204] Fri, 06 April 2007 00:35 Go to next message
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?
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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
icon14.gif  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 Go to previous message
plau18
Messages: 3
Registered: April 2007
Junior Member
That's great. It worked wonders. Thanks for the quick response.
Previous Topic: Help on questions faced at interview
Next Topic: next and pervious date
Goto Forum:
  


Current Time: Sat Dec 07 02:32:53 CST 2024