# Thread: Timestampdiff - Incorrect value??

1. Registered User
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. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
That is because the difference of 2 timestamps is a "duration". Then timestampdiff approximates that in whatever units (in you case days).

Andy

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

5. Registered User
Join Date
Oct 2008
Posts
23
Originally Posted by ARWinner
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
•