Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    23

    Unanswered: Timestampdiff - Incorrect value??

    Hi, would anyone know why the following, in DB2 9.7, would return:

    2099-11-25

    For the following:

    values date('1970-01-01') +
    timestampdiff(16, (timestamp('2099-12-31') - timestamp('1970-01-01') )) days

    I'm expecting it to return 2099-12-31.

    Because
    (1970-01-01) +
    The difference between (Any date > 1970-01-01) and '1970-01-01'

    ... Should be that "Any" date.

    Any thoughts?

  2. #2
    Join Date
    Oct 2008
    Posts
    23
    Err, I'm guessing its the days found in the leap years.

    31 day difference from what it should be.

    2099 - 1970 = 129

    Divided by 4, = 31 . . . Close enough for me.

    Seems like a bug to me . . . Wonder if its intended.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is because the difference of 2 timestamps is a "duration". Then timestampdiff approximates that in whatever units (in you case days).

    Andy

  4. #4
    Join Date
    Oct 2008
    Posts
    23
    Yep, documentation states it does not account for leap years.

  5. #5
    Join Date
    Oct 2008
    Posts
    23
    Quote Originally Posted by ARWinner View Post
    That is because the difference of 2 timestamps is a "duration". Then timestampdiff approximates that in whatever units (in you case days).

    Andy
    Yeah, that approximation seems a little silly, considering for days it is entirely calculable. IMO, dropping a day for each leap year across many years makes timestampdiff of limited use . . . Though apparently that's a known limitation.

Posting Permissions

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