datetime - DB2 timestampdiff function returning unexpected results

Question

I'm using the following syntax

TIMESTAMPDIFF(2, CHAR(CREATED - TIMESTAMP('1970-01-01 00:00:00'))

where CREATED is of type TIMESTAMP and the database is DB2. The intension is to get the timestamp converted to millis from epoch. If there is a better function that would be more helpful.

Sample data:
For 2011-10-04 13:54:50 returned value is 1316613290 but actual value should be 1317732890 (got from http://www.epochconverter.com)

Query to run

SELECT TIMESTAMPDIFF(2, CHAR(TIMESTAMP('2011-10-04 13:54:50') - TIMESTAMP('1970-01-01 00:00:00'))) FROM  SYSIBM.SYSDUMMY1;

Answer 1

This is the result of the fact that TIMESTAMPDIFF returns an estimate of the difference between the timestamps, not the actual value, as expected.

From the reference, page 435 (assuming for iSeries):

The following assumptions are used when converting the element values to the requested interval type:

• One year has 365 days.
• One year has 52 weeks.
• One year has 12 months.
• One quarter has 3 months.
• One month has 30 days.
• One week has 7 days.
• One day has 24 hours.
• One hour has 60 minutes.
• One minute has 60 seconds.
• One second has 1000000 microseconds.

And the actual calculation used is:

seconds + (minutes + (hours + ((days + (months * 30) + (years * 365)) * 24)) * 60) * 60

This is, for obvious reasons, inexact. Not helpful.

This appears to be a direct consequence of the way the timestamp arithmetic results are returned.
That is;

SELECT                                                              
TIMESTAMP('1971-03-02 00:00:00') - TIMESTAMP('1970-01-01 00:00:00') 
FROM sysibm/sysdummy1        

returns:

10,201,000,000.000000         

Which can be divided into:

• 1 year
• 02 months
• 01 days
• 00 hours
• 00 minutes
• 00 seconds
• 000000 microseconds

Which is imprecise period/duration information. While there are a multitude of situations where this type of data is useful, this isn't one of them.

Short answer: The exact answer cannot be correctly calculated in the database, and in fact should not.

---

Long answer:

The calculations are possible, but rather complex, and definitely not suited for in-database calculation. I'm not going to reproduce them here (look up JodaTime if you're interested, specifically the various Chronology subclasses). Your biggest problem is going to be the fact that months aren't all the same length. Also, you're going to run into major problems if your timestamps are anything other than UTC - more specifically, Daylight Savings time is going to play havoc with the calculation. Why? Because the offsets can change at any time, for any country.

Maybe you could explain why you need the number of milliseconds? Hopefully you're using Java (or able to do so), and can use java.time. But if you're on an iSeries, it's probably RPG...