TIMESTAMPDIFF Missing Days

Question

Can someone explain to me why this returns only 360 days and not 365 days?

I expect it to not count the first day but, what about the other 4 days?

SELECT
(TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2017-12-31') - TIMESTAMP('2017-01-01'))))
FROM sysibm.sysdummy1

I am planning on just adding + 5 at the end.

MichaelTiefenbacher · Accepted Answer · 2017-07-15T06:31:40.120

1

If you have DB2 for Linux, Unix and Windows - now called Db2 - Version 11.1 you could also use

SELECT DAYS_between('2017-12-31','2017-01-01') FROM SYSIBM.SYSDUMMY1

edited Jul 15 '17 at 06:31

answered Jul 14 '17 at 05:32

MichaelTiefenbacher

3,805
2
11
17

That is very nice! Can I ask where you found this function at? – user3183411 Jul 14 '17 at 20:47
It is all in the documentation - IBM Knowledge Center - specially the built-in functions can save you lots of work: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0011043.html – MichaelTiefenbacher Jul 15 '17 at 06:30

score 0 · Answer 2 · answered Jul 14 '17 at 02:55

I think the documentation explains this pretty well:

The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

In other words, the difference is 11 months and 30 days -- 11 * 30 + 30 = 360.

SELECT DAYS(DATE('2017-12-31')) - DAYS(DATE('2017-01-01'))
FROM sysibm.sysdummy1

For a more exact representation, try:

TIMESTAMPDIFF Missing Days

2 Answers2