本文介绍了DB2 timestampdiff函数返回意外结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我使用以下语法

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

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

08-04 13:32
查看更多