Help date/time issue

missmarm · #1 (**permalink**) 01-20-10, 03:29

I have 2 columns 1 datetime column is the endtime of the jobstep and the other is the runtime in microseconds type integer.

I want to calculate the starttime.

enddtime runtime(integer)
2010-01-01 12:05:09 5200

the starttime should be 12:05:09. Which function do i need to use?

I tried to convert to seconds /1000 that works but further i get errors. Timestampdiff is not what i needed.

Thanks!

dbzTHEdinosaur · #2 (**permalink**) 01-20-10, 03:48

previous post deleted by poster due to poor interpretation of question.

tonkuma · #3 (**permalink**) 01-20-10, 05:05

Here is an example to calculate starttime.
You can truncate or round the result to your reqired precision.
(Tested on DB2 9.7 on Windows/NT. But, It will work on z/OS, too.)

Code:

------------------------------ Commands Entered ------------------------------
SELECT endtime
     , runtime
     , endtime - runtime MICROSECONDS AS starttime
  FROM (SELECT TIMESTAMP('2010-01-01-12.05.09') , 5200 FROM sysibm.sysdummy1) t( endtime , runtime );
------------------------------------------------------------------------------

ENDTIME                    RUNTIME     STARTTIME                 
-------------------------- ----------- --------------------------
2010-01-01-12.05.09.000000        5200 2010-01-01-12.05.08.994800

  1 record(s) selected.

missmarm · #4 (**permalink**) 01-21-10, 02:58

It work but not the way it suppose to.
endtime runtime starttime
15-12-2009 20:07:32 5840 15-12-2009 20:07:31
14-12-2009 20:05:12 867 14-12-2009 20:05:11
14-12-2009 20:05:20 6894 14-12-2009 20:05:19
14-12-2009 20:11:40 379200 14-12-2009 20:11:39

ARWinner · #5 (**permalink**) 01-21-10, 08:38

Quote:

Originally Posted by missmarm

It work but not the way it suppose to.
endtime runtime starttime
15-12-2009 20:07:32 5840 15-12-2009 20:07:31
14-12-2009 20:05:12 867 14-12-2009 20:05:11
14-12-2009 20:05:20 6894 14-12-2009 20:05:19
14-12-2009 20:11:40 379200 14-12-2009 20:11:39

Sure it did. 1 microsecond = 0.000001 seconds. All of your runtimes are under 1 second, so subtracting that from the end time it will look like only 1 second when truncating the fractional seconds.

Andy