问题描述
我使用以下语法
TIMESTAMPDIFF(2,CHAR(CREATED - TIMESTAMP('1970-01-01 00:00:00'))
其中 CREATED
的类型是 TIMESTAMP
,数据库是DB2,意图是将时间戳从历元转换为毫秒,如果有更好的功能会更有帮助。
样本数据:
对于 2011-10-04 13:54:50
返回的值为 1316613290
,但实际值应为 1317732890
(从)
要运行的查询
SELECT TIMESTAMPDIFF(2,CHAR(TIMESTAMP('2011-10-04 13:54:50') - TIMESTAMP('1970-01-01 00:00 :00')))FROM SYSIBM.SYSDUMMY1;
这是因为 TIMESTAMPDIFF
返回一个估计的时间戳之间的差异,而不是实际值,如预期的那样。
从,第435页(假设为iSeries):
而实际使用的计算是:
这似乎是返回时间戳算术结果的方式的直接后果。
那就是
SELECT
TIMESTAMP('1971-03-02 00:00:00') - TIMESTAMP('1970-01-01 00: 00:00')
从sysibm / sysdummy1
返回:
10,201,000,000.000000
哪些可以分为:
-
1
年 -
02
月 -
01
days -
00
hours -
00
分钟 - code> 00 秒
-
000000
微秒
哪个是不精确的期间/持续时间信息。虽然这种类型的数据有许多情况有用,但这不是其中之一。
简短的答案:确切的答案不能在数据库中正确计算,实际上不应该。
长回答:
计算是可能的,但相当复杂,绝对不适用于数据库内计算。我不会在这里复制(如果您有兴趣,请查看),具体来说各种年表
子类)。你最大的问题是,几个月不一样的事实。而且,如果您的时间戳不是UTC,那么您将遇到重大问题 - 更具体地说,夏令时间将会对计算造成破坏。为什么?因为在任何时候,任何国家的抵消都可以改变。
也许你可以解释为什么你需要毫秒数?希望您使用Java(或能够这样做),并且可以使用 java.time
。但是如果你在一个iSeries上,可能是RPG ...
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;
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):
And the actual calculation used is:
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
year02
months01
days00
hours00
minutes00
seconds000000
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...
这篇关于DB2 timestampdiff函数返回意外结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!