在DB2 Universal Database中转换UTC时间戳_数据库

在DB2 Universal Database中转换UTC时间戳

发表于：2007-07-13来源：作者：点击数：标签：

简介本文展示了一个 DB2 用户定义函数（UDF）的 Java 实现的设计，该函数有两个输入参数：一个 UTC 时间戳（例如 2004-04-04-04.00.00.000000）和一个地区名（例如 "America/Guayaquil"），并返回新地区中对应的时间戳（在这里是 2004-04-03-23.00.00.00000

简介

本文展示了一个 DB2 用户定义函数（UDF）的 Java 实现的设计，该函数有两个输入参数：一个 UTC 时间戳（例如 2004-04-04-04.00.00.000000）和一个地区名（例如 "America/Guayaquil"），并返回新地区中对应的时间戳（在这里是 2004-04-03-23.00.00.000000）。

执行这种转换时遇到的两个主要挑战是：

要支持某个地区可能出现的所有不同名称，以及可能作为输入参数传入的所有不同地区。
对于每个地区，要计算出夏令时（daylight savings time）规则。

对于包含来自很多不同应用程序和分布在几个洲的地区的数据的数据仓库项目，经验证明，在转换方面，该函数对于 Extract Transform Load (ETL) 在分析和处理数据时需要用到的过程极其有用。

背景

时间连续区间（time continuum）内的某个惟一时刻可以由那一刻的日期、时间来定义，为了更精确起见，还可以加上 秒后面的小数。这种定义叫做 时间戳，它在 DB2 中的 ISO 表示如下：

clearcase/" target="_blank" >cccccc border=1>



2004-07-24-16.18.28.410002

在这种情况下，精确度可以下调为 1 微秒。

在一个与某个事务有关的事件发生时记录下时间戳，这一活动就叫做为该事务“记录时间戳（timestamping）”。在一个事务的整个生命周期内，需要多次记录时间戳，以便记下创建、最后一次修改、最后一次访问某一事务的时间。

对于发生在多个地理位置和多个地区的集中存储事务，常见的设计是用与该地区时间对应的世界协调时间（Universal Time Coordinated，UTC）记下每个时间戳记录。通过同时记录下事务发生时所在的位置，例如存储事务发生时所在的客户号或业务部门号，可以重新构造该事务的本地时间。

当必须处理、分析和报告来自大量不同来源的数据时，我们需要重新构造 UTC 时间戳在事务原始位置的本地时间。常被问到的问题有：

我知道 UTC 时间，但是客户的本地时间是什么呢？
是上午还是下午？
是在我们的地理区域的工作时间之内，还是在该工作时间之外？
其他问题。

挑战

表面上看起来既简单又容易完成的任务，事实上被证明是既不简单也不容易完成。

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

我们遇到的一个挑战是理解我们所接收到的地区名：

对于同一个地区，我们数据仓库的每个数据源都为我们提供了不同的名称，例如 Eastern Daylight、Eastern Standard Time、America/New_York、EST 等 —— 其实意思都是一样。
每个数据源都处理一组不同的地区。例如，某个数据源只有 North American 地区，而其他数据源在欧洲也有办事处，因而就需要一个更大的地区列表。

另一个挑战是实现我们处理的所有地区的夏令时（Daylight Savings Time，DST）规则 —— 这些规则我们还不能轻松地使用。

还有一个需求就是能够很容易地使用用来调用转换函数的 API。

例如，为了得到一个 UTC 时间戳在 "America/Nassau" 的本地时间，一家虚构的 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"。这就是类方法为了进行时间戳转换而在内部使用的值。

注意：这里鼓励为时区使用长名，例如 "America/New_York"。但是在这个特定的实现中，我们使用了短名称，因为 UDB DB2 version 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 日历，然后通过格式转换器（formatter）产生一个新的转换后的时间戳。细微部分没有进行转换，直接变成输出，因为 Java Calendar 没有精确到那个程度。

可以用下面的语句将该 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;

    }

可以从 SQL 中调用上面的 DB2 UDF，但是为了方便起见，我们创建另一个 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 正逐渐成为大家喜爱的 Greenwich Mean Time (GMT) 的同义词。

结束语

在数据仓库项目的 ETL 过程中，将时间戳从一个地区转换为另一个地区的函数被证明是转换方面的主力。本文提供了以 UDB DB2 UDF 的形式运行的这类 Java 函数的代码。

原文转自：http://www.ltesting.net

软件测试 > 测试开发技术 > 软件测试开发语言 > 数据库 >