DB2 timestampdiff 函数返回意外结果 [英] DB2 timestampdiff function returning unexpected results

问题描述

我正在使用以下语法

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

其中CREATED 是TIMESTAMP 类型，数据库是DB2.目的是将时间戳从纪元转换为毫秒.如果有更好的功能会更有帮助.

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.

样本数据:
对于 2011-10-04 13:54:50，返回值为 1316613290 但实际值​​应为 1317732890(来自 http://www.epochconverter.com)

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)

要运行的查询

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

推荐答案

这是 TIMESTAMPDIFF 返回时间戳之间差异的估计的结果，正如预期的那样，不是 实际 值.

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

来自 参考，页面435(假设为 iSeries):

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

转换元素值时使用以下假设到请求的间隔类型:

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

• 一年有 365 天.
• 一年有 52 周.
• 一年有 12 个月.
• 一个季度有 3 个月.
• 一个月有 30 天.
• 一周有 7 天.
• 一天有 24 小时.
• 一小时有 60 分钟.
• 一分钟有 60 秒.
• 一秒有 1000000 微秒.

而实际使用的计算是:

秒 + (分钟 + (小时 + ((天 + (月 * 30) + (年 * 365)) * 24)) * 60) * 60

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        

返回:

10,201,000,000.000000         

Which can be divided into:

• 1年
• 02 月
• 01 天
• 00 小时
• 00 分钟
• 00 秒
• 000000 微秒
• 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.

长答案:

计算是可能的，但相当复杂，绝对不适合数据库内计算.我不会在这里复制它们(如果您有兴趣，请查找 JodaTime，特别是各种 Chronology 子类).您最大的问题将是月份的长度不尽相同的事实.此外，如果您的时间戳不是 UTC，您将遇到重大问题 - 更具体地说，夏令时将对计算造成严重破坏.为什么?因为对于任何国家，偏移量可以随时更改.

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.

也许你可以解释一下为什么你需要毫秒数?希望您正在使用 Java(或能够这样做)，并且可以使用 java.time.但是，如果您使用的是 iSeries，那可能是 RPG...

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

这篇关于DB2 timestampdiff 函数返回意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！