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...
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…