MaxCompute TIMESTAMP_NTZ数据类型

背景

当定义一个包含MaxCompute 2.0 TIMESTAMP字段类型的表时，该字段会记录自Epoch（UTC 1970-01-01 00:00:00）起始的时间偏移量。该类型在内部存储上不包含时区信息，但显示结果可能受制于查询或展示时系统所采用的当前时区设置，意味着实际展现的时间值会根据时区差异进行转换。MaxCompute支持的时区列表详情，请参见时区列表。

例如：TIMESTAMP字段类型值在东八区显示为1970-01-01 00:00:00，如果把当前的时区切换为UTC，则显示为1969-12-31 16:00:00，代码示例如下。

1. 开启MaxCompute 2.0数据类型并确认时区。

   --开启2.0数据类型
   SET odps.sql.type.system.odps2=true;
   --MaxCompute Project时区默认是中国的东八区（Asia/Shanghai），通过以下命令返回的odps.sql.timezone属性，确认前时区
   setproject;
   --若当前项目非东八区，执行以下代码
   SET odps.sql.timezone=Asia/Shanghai;

2. 定义包含TIMESTAMP字段类型的表ts_test并查询表数据。

   --创建一张表用来测试TIMESTAMP的行为
   CREATE TABLE ts_test(ts timestamp) lifecycle 1;
   --向表中插入一条记录
   INSERT INTO TABLE ts_test VALUES(timestamp '1970-01-01 00:00:00');
   --查询表中数据
   SELECT * FROM ts_test;

   返回结果如下。

   --输出如下结果：
   +------+
   | ts   |
   +------+
   | 1970-01-01 00:00:00 |
   +------+

3. 修改当前时区并查询数据。

   --修改当前的时区为UTC
   SET odps.sql.timezone=UTC;
   
   --修改时区后，查询表中数据
   SELECT * FROM ts_test;

   返回结果如下。

   --输出结果如下：
   +------+
   | ts   |
   +------+
   | 1969-12-31 16:00:00 |
   +------+

MaxCompute上述行为与Hive 2一致，但是Hive 3版本TIMESTAMP不依赖于当前时区设置，且Hive 3关于TIMESTAMP行为符合SQL标准（2003及以上）。在Hive 3中行为示例如下。

--设置时区为东8区
SET time zone Asia/Shanghai;

--创建测试用的表
CREATE TABLE ts_test(a timestamp);

--向表中插入数据
INSERT INTO TABLE ts_test VALUES(timestamp '1970-01-01 00:00:00');

--读表中的数据
SELECT * FROM ts_test;
--结果如下：
1970-01-01 00:00:00

--测试bigint类型cast成timestamp类型的结果
SELECT cast(0L AS timestamp);
--结果如下：
1970-01-01 00:00:00

--将时区修改为UTC
SET time zone UTC;

--重新读表中的数据
SELECT * FROM ts_test;
--结果如下：
1970-01-01 00:00:00

--在新时区中测试bigint类型cast成timestamp类型的结果
SELECT cast(0L AS timestamp);
--结果如下：
1970-01-01 00:00:00

由于MaxCompute是商用系统，需要兼容Hive 2版本，不得修改已有的TIMESTAMP的行为。同时，还需要考虑兼容Hive 3的行为并符合SQL标准。因此，引入一种新的数据类型，即无时区时间戳类型（TIMESTAMP_NTZ）。

使用限制

• 不支持Hologres对TIMESTAMP_NTZ数据类型进行读写。

• 不支持PAI发起的AlgoTask、PS作业类型对该数据类型进行读写操作。

• 如果您使用本地客户端（odpscmd）连接MaxCompute，必须确保odpscmd版本为V0.46及以上。

定义

在启用MaxCompute 2.0数据类型之后，创建表时可以指定字段类型为TIMESTAMP_NTZ。使用示例如下。

1. 开启MaxCompute 2.0数据类型并确认时区。

   --开启2.0数据类型
   SET odps.sql.type.system.odps2=true;
   --MaxCompute Project时区默认是中国的东八区（Asia/Shanghai），通过以下命令返回的odps.sql.timezone属性，确认前时区
   setproject;
   --若当前项目非东八区，执行以下代码。
   SET odps.sql.timezone=Asia/Shanghai;

2. 创建表ts_test01并查询表数据。

   --创建表，有两个字段，类型分别为timestamp和timestamp_ntz，用于对比两者之间行为的差异
   CREATE TABLE ts_test02(a timestamp, b timestamp_ntz);
   --向表中插入数据
   INSERT INTO TABLE ts_test02 VALUES(timestamp '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00');
   
   --查询表中数据
   SELECT * FROM ts_test02;

   返回结果如下。

   --结果如下
   +------+------+
   | a    | b    |
   +------+------+
   | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
   +------+------+

3. 修改当前时区并查询数据。

   --修改当前的时区为UTC
   SET odps.sql.timezone=UTC;
   --重新查询表中数据
   SELECT * FROM ts_test02;

   返回结果如下。

   --注意：a的数据类型是timestamp，它的显示结果已经变了；b的数据类型是timestamp_ntz，它的显示结果保持不变
   +------+------+
   | a    | b    |
   +------+------+
   | 1969-12-31 16:00:00 | 1970-01-01 00:00:00 |
   +------+------+

生成TIMESTAMP_NTZ类型数据

--开启2.0数据类型
SET odps.sql.type.system.odps2=true;

--设置odps.sql.timestamp.function.ntz为true
SET odps.sql.timestamp.function.ntz=true;
--调用3个函数
SELECT current_timestamp() AS current_result, from_utc_timestamp(0L, 'UTC') AS from_result, to_utc_timestamp(0L, 'UTC') as to_result;

--结果如下，注意current_timestamp的运行结果和当前的系统时间相关
+----------------+-------------+-----------+
| current_result | from_result | to_result |
+----------------+-------------+-----------+
| 2023-07-01 21:22:39.066 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
+----------------+-------------+-----------+

EXPLAIN SELECT current_timestamp() AS current_result, from_utc_timestamp(0L, 'UTC') AS from_result, to_utc_timestamp(0L, 'UTC') as to_result;

--有如下的片段，说明输出结果中current_result/from_result/to_result这3个字段的数据类型是TIMESTAMP_NTZ类型
            FS: output: Screen
                schema:
                  current_result (timestamp_ntz)
                  from_result (timestamp_ntz)
                  to_result (timestamp_ntz)

支持的运算

关系运算

MaxCompute TIMESTAMP_NTZ数据类型，支持以下关系运算符，关于关系运算符详情，请参见关系运算符。

• 支持Equals（=）、Not Equals（!=）和Eqns（<=>）运算符。

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  
  --支持Equals、Not Equals和Eqns操作
  SELECT a = b AS eq_result, a != b AS neq_result, a <=> b AS eqns_result FROM VALUES(timestamp_ntz '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00') AS t(a, b);

  返回结果如下。

  +-----------+------------+-------------+
  | eq_result | neq_result | eqns_result |
  +-----------+------------+-------------+
  | true      | false      | true        |
  +-----------+------------+-------------+

• 支持GT（>）、GE（>=）、LT（<）和LE（<=）运算符。

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  SELECT a > b AS gt_result, a >= b AS ge_result, a < b AS lt_result, a <= b AS le_result FROM VALUES(timestamp_ntz '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00') AS t(a, b);

  返回结果如下。

  +-----------+-----------+-----------+-----------+
  | gt_result | ge_result | lt_result | le_result |
  +-----------+-----------+-----------+-----------+
  | false     | true      | false     | true      |
  +-----------+-----------+-----------+-----------+

算术运算

• 支持两个TIMESTAMP_NTZ类型数据相减，相减的结果是INTERVAL_DAY_TIME类型。

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  --两个TIMESTAMP_NTZ类型数据相减
  SELECT timestamp_ntz '1970-01-01 00:01:30' - timestamp_ntz '1970-01-01 00:00:00';

  返回结果如下。

  运行结果如下：
  +------+
  | _c0  |
  +------+
  | 0 00:01:30.000000000 |
  +------+

• 支持TIMESTAMP_NTZ类型加减INTERVAL_YEAR_MONTH类型。

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  --TIMESTAMP_NTZ类型加减INTERVAL_YEAR_MONTH类型
  SELECT a+b AS plus_result, a-b AS minus_result FROM VALUES(timestamp_ntz '1970-01-01 00:00:00', interval '1' year) AS t(a, b);

  返回结果如下。

  +-------------+--------------+
  | plus_result | minus_result |
  +-------------+--------------+
  | 1971-01-01 00:00:00 | 1969-01-01 00:00:00 |
  +-------------+--------------+

• 支持TIMESTAMP_NTZ类型加减INTERVAL_DAY_TIME类型。

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  --TIMESTAMP_NTZ类型加减INTERVAL_DAY_TIME类型
  SELECT a+b AS plus_result, a-b AS minus_result FROM VALUES(timestamp_ntz '1970-01-01 00:00:00', interval '1' day) AS t(a, b);

  返回结果如下。

  +-------------+--------------+
  | plus_result | minus_result |
  +-------------+--------------+
  | 1970-01-02 00:00:00 | 1969-12-31 00:00:00 |
  +-------------+--------------+

日期和时间函数

支持以TIMESTAMP类型为输入参数，也支持以TIMESTAMP_NTZ为输入参数。关于日期与时间函数详情，请参见日期与时间函数。

• 示例1：DATEADD函数

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  select dateadd(a, 1, 'dd') AS a_result, dateadd(b, 1, 'dd') AS b_result FROM VALUES(timestamp '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00') t(a, b);

  返回结果如下。

  +----------+----------+
  | a_result | b_result |
  +----------+----------+
  | 1970-01-02 00:00:00 | 1970-01-02 00:00:00 |
  +----------+----------+

• 示例2：MONTH函数

  --开启2.0数据类型
  SET odps.sql.type.system.odps2=true;
  SELECT month(a) AS a_result, month(b) AS b_result FROM VALUES(timestamp '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00') t(a, b);

  返回结果如下。

  +----------+----------+
  | a_result | b_result |
  +----------+----------+
  | 1        | 1        |
  +----------+----------+

聚合函数

TIMESTAMP_NTZ类型数据支持MAX和MIN聚合函数。示例如下。

--开启2.0数据类型
SET odps.sql.type.system.odps2=true;
SELECT max(a) AS max_result, min(a) AS min_result FROM VALUES (timestamp_ntz '1970-01-01 00:00:00'), (timestamp_ntz '1970-01-01 01:00:00'), (timestamp_ntz '1970-01-01 02:00:00') AS t(a);

返回结果如下。

+------------+------------+
| max_result | min_result |
+------------+------------+
| 1970-01-01 02:00:00 | 1970-01-01 00:00:00 |
+------------+------------+

自定义UDF

自定义Java UDF类java.time.LocalDateTime支持输入和输出参数类型是TIMESTAMP_NTZ类型。为了方便演示，这里使用了嵌入式UDF功能，详情请参见UDF（嵌入式）。

您需要通过脚本模式提交执行，关于脚本模式详情，请参见SQL脚本模式。

--开启2.0数据类型
SET odps.sql.type.system.odps2=true;
--定义foo_udf函数：输入timestamp_ntz变量，保持年月日时分秒不变，但是把它的毫秒修改为999
CREATE TEMPORARY FUNCTION foo_udf AS 'com.mypackage.Test' USING
#CODE ('lang'='JAVA')
package com.mypackage;
import com.aliyun.odps.udf.UDF;
public class Test extends UDF {
  public java.time.LocalDateTime evaluate(java.time.LocalDateTime ld) {
    if (ld == null) return null;
    java.time.LocalDateTime result = java.time.LocalDateTime.of(
        ld.getYear(), ld.getMonthValue(), ld.getDayOfMonth(),
        ld.getHour(), ld.getMinute(), ld.getSecond(), 999000000);
    return result;
  }
}
#END CODE;
--输入timestamp_ntz变量1970-01-01 00:00:00传入foo_udf函数并查询其处理结果
SELECT foo_udf(a) FROM VALUES(timestamp_ntz '1970-01-01 00:00:00') AS t(a);

返回结果如下。

+------+
| _c0  |
+------+
| 1970-01-01 00:00:00.999 |
+------+