Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Help date/time issue

    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!

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    previous post deleted by poster due to poor interpretation of question.
    Last edited by dbzTHEdinosaur; 01-20-10 at 06:20. Reason: realized my answer was incorrect - wrong

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 01-20-10 at 06:10.

  4. #4
    Join Date
    Jan 2010
    Posts
    2
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by missmarm View Post
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    missmarm,

    I wrote
    Here is an example to calculate starttime.
    You can truncate or round the result to your reqired precision.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •