db2数据库插入时间戳_在DB2通用数据库中转换通用时间协调时间戳

重要：在阅读本文之前，请阅读免责声明 。

本文介绍了针对DB2用户定义函数（UDF）的Java实现的设计，该函数以两个参数作为输入：UTC时间戳记（例如2004-04-04-04.00.00.000000）和语言环境名称（例如“美国/瓜亚基尔”），并在新的语言环境中返回等效的时间戳记（在本例中为2004-04-03-23.00.00.000000）。

执行转换的两个主要挑战是：

• 支持某种语言环境的所有不同名称的能力可能会出现，并且可能会作为输入出现所有不同的语言环境
• 能够确定每个区域的夏令时规则

数据仓库项目的实际生活经验是，数据来自许多不同的应用程序，并且分布在几个大洲的不同地区，这证明了此功能对于提取转换负载（ETL）流程的转换方面而言是无价的，而该过程需要分析和分析。处理数据。

做好准备

时间连续体中的唯一时刻可以通过该时刻的日期 ， 时间来定义，并且甚至可以更精确地通过一秒的分数来定义 。 称为时间戳记 ，它在DB2中的ISO表示如下：

2004-07-24-16.18.28.410002

在这种情况下，精度下降到一微秒。

记录与事务相关的事件发生时的时间戳记称为“时间戳记”事务。在整个记录生命周期中多次对记录加上时间戳记以记录创建事务的时间，最后一次修改，最后一次访问，依此类推。

集中存储发生在多个地区和多个区域中的事务的通常设计是使用与本地时间等效的通用时间协调（UTC）对每个记录进行时间戳记。 通过还记录交易发生的位置（例如，通过存储交易发生的客户编号或业务单位编号），可以重构该交易的本地时间。

当必须处理，分析和报告来自许多来源的数据时，向我们提出了在交易的原始位置重建UTC时间戳的本地时间的需求。 提出的典型问题是：

• 我知道UTC时间是多少，但是客户的本地时间是多少？
• 是早上还是下午？
• 是在我们针对该地理位置的工作时间之内还是下班后？
• 等等。

挑战

看来既简单又容易获得的练习却没有成功。

从本地时间到UTC的实时转换通常是在应用程序中完成的。 它仅适用于当前时间（不适用于过去或将来的任何其他时间点），并且仅适用于运行应用程序的服务器的语言环境（不适用于任何其他语言环境）。

我们面临的挑战之一是弄清给我们的语言环境名称：

• 数据仓库的每个数据源都为我们提供了相同区域设置的不同名称，例如，Eastern Daylight，Eastern Standard Time，America / New_York，EST等，所有这些都具有相同的含义。
• 我们数据的每个来源都在处理另一组语言环境。 例如，由于一个数据源仅具有北美地区，因此其他数据源也在欧洲设有办事处，因此需要扩展的地区列表

另一个挑战是在我们正在处理的所有语言环境中实施夏令时（DST）规则-我们无法轻松访问的规则。

调用转换函数也是非常容易使用的API之一。

例如，要检索UTC时间戳的“美国/拿骚”本地时间，请使用虚构的Acme Intl的SELECT语句。 公司将不得不看起来像这样简单：

SELECT ACME.F_CONVERT_TIMEZONE(TRANSACTION_TIMESTAMP, "America/Nassau")
FROM ACME.TRANSACTION_TABLE;

解决方案

一些数据源由Java应用程序填充，结果，每个应用程序用于其语言环境的名称都是在不同的Java JDK版本（1.1.8、1.4等）中找到的名称。 由于Java JDK还已经为相当全面的语言环境列表编码了所有DST规则，因此我们选择用Java编写转换函数并在DB2的Java JDK上运行它。

为了易于使用和方便，将Java类包装在另一个DB2 UDF中。

解决方案详细信息

Java类具有一个类变量，该变量存储查找字典，其中包含所有可能作为输入而来的语言环境的语言环境的所有拼写和命名。

tz_map = new Hashtable();
	â??¦ 
	tz_map.put("Eastern Daylight", 		"EST");
	tz_map.put("Eastern Standard Time", 	"EST");
	tz_map.put("America/New_York", 		"EST");
	â??¦

例如，上述所有键均对应于值¢??? EST”。该值将由class方法在内部用于时间戳转换。

注意：鼓励在时区使用长名，例如“ America / New_York”。 在此特定实现中，我们改用短名称，因为UDB DB2版本7.2使用仅可使用短名称的JDK 1.1.8。

查找表的填充是手动完成的。 在查找每个语言环境的内部Java设置时需要付出很大的努力，并且将长名与具有相同DST规则和时区的短名配对。

以此方式映射了250多个语言环境。 可以根据需要添加新的内容。 这提供了我们所需的灵活性，因为新的数据源将与新的语言环境一起添加到数据仓库中。

对于实际的时间戳转换，使用了以下类方法：

public static java.lang.String J_CONVERT_TIMEZONE(java.lang.String 
ivc_UTCtimestamp, java.lang.String ivc_timezone)

首先将输入的时间戳字符串解析为其组件，从这些值实例化Java日历，然后使用格式化程序生成新的转换后的时间戳。 由于Java日历不具有这种精度，因此不经过转换就传递了微秒。

以下语句可用于将Java类方法注册为UDF函数：

public static java.lang.String J_CONVERT_TIMEZONE
(java.lang.String ivc_UTCtimestamp, 
 java.lang.String ivc_timezone) 
throws Exception
    {
        // get the short name equivalent of the input
        ivc_timezone = (String)tz_map.get(ivc_timezone);
        if (ivc_timezone == null)
            ivc_timezone = "GMT"; // default to UTC if entry not found

        // replace the . with - so that we only have one token separator instead of two
        String ivc_UTCtimestamp_new = ivc_UTCtimestamp.replace('.', '-' );

        // parse, validate and convert the TS string to integers, based on the one
        // separator
        StringTokenizer st = new StringTokenizer(ivc_UTCtimestamp_new, "-");
        int year = Integer.parseInt(st.nextToken());
        int month = Integer.parseInt(st.nextToken());
        int day = Integer.parseInt(st.nextToken());
        int hour = Integer.parseInt(st.nextToken());
        int min = Integer.parseInt(st.nextToken());
        int sec = Integer.parseInt(st.nextToken());
        String micro = st.nextToken(); // just carried over from the input

        // create with the above a calendar in UTC
        Calendar calUTC = Calendar.getInstance();
        calUTC.clear();
        calUTC.setTimeZone(TimeZone.getTimeZone("GMT"));
        calUTC.set(year, month-1, day, hour, min, sec );

        // prepare the formatter for the specified timezone
        DateFormat formatter = new 
            SimpleDateFormat("yyyy'-'MM'-'dd'-'HH.mm.ss", Locale.US);
        TimeZone tz = TimeZone.getTimeZone(ivc_timezone);
        formatter.setTimeZone(tz);

        // return the new value
        return formatter.format(calUTC.getTime()) + "." + micro;
    }

上面的DB2 UDF可以从SQL中调用，但是为了方便起见，创建了另一个DB2 UDF将DB2时间戳的输入转换为字符串，并将字符串的输出转换回DB2函数，以便输入和输入输出是与DB2兼容的时间戳。 代码如下：

CREATE FUNCTION ACME.F_CONVERT_TIMEZONE (
        IPTS_TIMESTAMP  TIMESTAMP,
        IPCH_TIMEZONE   VARCHAR(30))
RETURNS TIMESTAMP

BEGIN ATOMIC

   DECLARE vvch_result VARCHAR(30);

   SET vvch_result = j_convert_timezone(char(IPTS_TIMESTAMP), rtrim(IPCH_TIMEZONE));

   RETURN CASE vvch_result
      WHEN 'null' THEN NULL
      ELSE timestamp(vvch_result)
   END;

END

结果，可以按照以下简单SQL语句来调用该函数：

SELECT ACME.F_CONVERT_TIMEZONE(TRANSACTION_TIMESTAMP, "America/Nassau")
FROM ACME.TRANSACTION_TABLE;

未来的改进和变化

以下是可以轻松添加以增强此解决方案的更改：

• 添加新的语言环境（通过扩展查找字典）。
• 将新的同义词添加到语言环境（通过扩展查找字典）。
• 随着更高版本的JDK可用，您可以开始使用长名称进行内部转换（更新Java代码）。
• 创建一个反向函数-该函数将为某个区域设置时间戳并返回等效的UTC时间戳。

注意：UTC是格林威治标准时间（GMT）越来越受欢迎的同义词。

结论

将时间戳从一种语言环境转换为另一种语言的功能可能被证明是数据仓库项目的ETL流程中常见的转换方面的主要内容。 本文提供了作为UDB DB2 UDF运行的Java函数的代码。

致谢

我要感谢IBM多伦多实验室的数据库顾问Paul Yip所提供的宝贵帮助和设计建议。

我要感谢IBM加拿大全球服务部的James Liu，他对查找字典进行了详细的手动映射，并对功能进行了非常彻底的边界测试，最后但同样重要的是，Jammie Lee，他为协调团队的工作和积极的工作做出了贡献。反馈以使之前进并实现。

免责声明

本文包含示例代码。 IBM授予您（“被许可方”）非排他性的免版税许可，以使用此示例代码。 但是，示例代码按原样提供，没有任何明示或暗示的保证，包括对适销性，特定用途的适用性或非侵权性的任何暗示保证。 对于因使用本软件而导致的被许可人所遭受的任何损害，IBM及其许可人概不负责。 IBM或其许可方在任何情况下均不对任何损失，收益或数据，或直接，间接，特殊，继发性，偶发性或惩罚性损害负责，无论是由责任理论引起的还是基于该理论的或无法使用软件，即使IBM已被告知可能发生此类损害。

---

翻译自: https://www.ibm.com/developerworks/data/library/techarticle/dm-0407munteanu/index.html