Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    7

    Unanswered: Calculating with timestamps

    Hi all,

    I have a problem with calculating a duration from two timestamps.

    There are two columns in a table "start_ts" and "end_ts" both being of type TIMESTAMP. Now i would like to calculate a duration by subtracting end_ts from start_ts. The result should be put into a column "duration" which is of type TIME.

    This is my code :

    UPDATE JOB_STATISTICS
    SET DURATION=END_TS-START_TS
    WHERE DURATION='00:00:00' AND JOB_NAME='...'

    I get the following error:
    SQL0408N A value is not compatible with the data type of its assignment
    target. Target name is "DURATION". SQLSTATE=42821

    How can I type-cast the calculation so the result will be of type TIME?

  2. #2
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Your could use TIME function. Check out the following:
    Code:
    D:\>db2 "create table t(name time)"
    DB20000I  The SQL command completed successfully.
    
    D:\>db2 describe table t
    
    Column                         Type      Type
    name                           schema    name               Length   Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    NAME                           SYSIBM    TIME                      3     0 Yes
    
      1 record(s) selected.
    
    
    D:\>db2 "insert into t values(time(current timestamp))"
    DB20000I  The SQL command completed successfully.
    
    D:\>db2 "select * from t"
    
    NAME
    --------
    09:42:59
    
      1 record(s) selected.
    
    
    D:\>
    Last edited by ggnanaraj; 07-20-06 at 01:19.

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    When subtracting one timestamp from another, the result is a timestamp-duration ( not a time ). To store the result as TIME, some calculations/transformations are necessary.

    Try this:


    ... SET DURATION=TIME('00:00:00') + cast ((END_TS - START_TS ) as decimal(6,0)) ...

  4. #4
    Join Date
    Dec 2004
    Posts
    7
    Thanks for the replies so far.

    I tried the following:

    UPDATE JOB_STATISTICS
    SET DURATION=TIME('00:00:00') + cast ((END_TS - START_TS ) as decimal(6,0))
    WHERE DURATION='00:00:00' AND JOB_NAME='...'


    And got this error message:
    SQL0413N Overflow occurred during numeric data type conversion.
    SQLSTATE=22003


    Fiddled around with some variations like "cast ((END_TS - START_TS ) as decimal(10,0)) because the timestamps are of length 10 (was just a guess..). I then got this error message:
    SQL0182N An expression with a datetime value or a labeled duration is not
    valid. SQLSTATE=42816


    Something like this didnt work either:
    SET DURATION=TIME(END_TS )- TIME(START_TS )
    or
    SET DURATION=TIME(END_TS -START_TS )
    Last edited by mc_fly; 07-20-06 at 09:22.

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    what is the maximum difference between END_TS and START_TS
    ... if its more than 24 hours you can't store it in an column of type TIME.


    Indeed subtracting two timestamps results in an DEC(20,6) number
    consisting of:
    yyyymmddhhmmss,zzzzz

    where yyyy is the difference of years,
    mm is the difference of month, dd of days and so on till zzzzz the difference of microseconds.

    if you calculate:
    ts1 = ' 2006-07-20-15.31.08.135090' and
    ts2 = ' 2006-07-21-15.34.18.135090'

    ts2 - ts1 will result in the decimal number
    00000001000310,000000
    ( difference is 0 years, 0 month, 1 day, 0 hours, 3 minutes and 10 seconds )


    so, if the maximum difference of the timestamps is less than 24 hours, the maximum value you may receive is

    00000000235959,999999
    ( 23 hours, 59 minutes, 59 seconds and 999999 microseconds )
    this number can be cast to a decimal(6,0) which can be interpreted as an time duration and -by added to time 00:00- be trasformed to a TIME-type


    if the maximum difference is more than 24 hours, you can't it store in an TIME column

  6. #6
    Join Date
    Dec 2004
    Posts
    7
    Ah, this makes sense. The duration in my test-environment is indeed longer then 24 hours.

    Thank you very much for the detailed analysis.

  7. #7
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    why dont u chk out the timestampdiff function. chk out more at

    http://publib.boulder.ibm.com/infoce...61%6d%70%22%20

    db2 values( TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -TIMESTAMP('2001-09-26-12.07.58.065497'))) )

    will give u the difference in minutes which can easily be converted into days/hrs.

    regards,
    Rahul

Posting Permissions

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