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;

Solution 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...

Solution 2

According to the v9.7 info center, TIMESTAMPDIFF returns an estimated time difference, based on 365 days in a year (not true ~25% of the time), 30 days in a month (not true 75% of the time, though averages out a bit better than that), 24 hours in a day (not true a couple days of the year in some timezones), 60 minutes in an hour (hooray, one right!), and 60 seconds in a minute (true >99.9% of the time - we do get leap seconds).

So, no, this is not the way to get epoch time in DB2. Thus far, I've resorted to getting the time as a timestamp, and converting it in the client.

Solution 3

Part of your error occurs because of the inaccuracy of the TIMESTAMPDIFF function, as others have pointed out.

The other source of error occurs because the Epoch is based on GMT – so you have to take your local timezone into account.

So, you can do this with the following expression:

(DAYS(timestamp('2011-10-04-13.54.50.000000') - current timezone) - DAYS('1970-01-01-00.00.00.000000')) * 86400 + MIDNIGHT_SECONDS(timestamp('2011-10-04-13.54.50.000000') - current timezone)

You can write a simple UDF to simplify this:

create or replace function epoch (in db2ts timestamp)
   returns bigint
   language sql
   deterministic
   no external action
   return (days(db2ts - current timezone) - days('1970-01-01-00.00.00.000000')) * 86400 + midnight_seconds(db2ts - current timezone);

Good luck,