文档

MaxCompute TIMESTAMP_NTZ数据类型

更新时间:

MaxCompute 2.0新增了无时区时间戳类型(TIMESTAMP_NTZ),该类型表示不包含时区信息的日期和时间点,所有数据均基于统一的时间基准(如UTC)。相较于其他带有时区的时间戳类型(TIMESTAMP),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类型数据

TIMESTAMP_NTZ常量

  • 语法

    TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789'
  • 示例

    --返回结果2017-11-11 00:00:00.123456789
    SELECT TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789';

从其他类型转换

MaxCompute支持通过CAST函数,将数据类型转换为TIMESTAMP_NTZ类型。

  • 示例1:将各种时间类型转换为TIMESTAMP_NTZ类型

    --开启2.0数据类型
    SET odps.sql.type.system.odps2=true;
    
    --将各种时间类型CAST成TIMESTAMP_NTZ类型
    SELECT cast(date '1970-01-01' AS timestamp_ntz) AS date_cast_result, cast(datetime '1970-01-01 00:00:00' AS timestamp_ntz) AS datetime_cast_result, cast(timestamp '1970-01-01 00:00:00' AS timestamp_ntz) AS timestamp_cast_result;

    返回结果如下。

    +------------------+----------------------+-----------------------+
    | date_cast_result | datetime_cast_result | timestamp_cast_result |
    +------------------+----------------------+-----------------------+
    | 1970-01-01 00:00:00 | 1970-01-01 00:00:00  | 1970-01-01 00:00:00   |
    +------------------+----------------------+-----------------------+
  • 示例2:将数值类型转换为TIMESTAMP_NTZ类型

    --开启2.0数据类型
    SET odps.sql.type.system.odps2=true;
    --将数值类型CAST成TIMESTAMP_NTZ类型
    SELECT cast(1L AS timestamp_ntz) AS bigint_cast_result, cast(1BD AS timestamp_ntz) AS decimal_cast_result, cast(1.5f AS timestamp_ntz) As float_cast_result, cast(1.5 AS timestamp_ntz) AS double_cast_result;

    返回结果如下。

    +--------------------+---------------------+-------------------+--------------------+
    | bigint_cast_result | decimal_cast_result | float_cast_result | double_cast_result |
    +--------------------+---------------------+-------------------+--------------------+
    | 1970-01-01 00:00:01 | 1970-01-01 00:00:01 | 1970-01-01 00:00:01.5 | 1970-01-01 00:00:01.5 |
    +--------------------+---------------------+-------------------+--------------------+
  • 示例3:将字符类型转换为TIMESTAMP_NTZ类型

    --开启2.0数据类型
    SET odps.sql.type.system.odps2=true;
    --将字符类型转换成TIMESTAMP_NTZ类型
    SELECT cast(s AS timestamp_ntz) AS string_cast_result, cast(cast(s AS char(50)) AS timestamp_ntz) AS char_cast_result, cast(cast(s AS varchar(100)) AS timestamp_ntz) AS varchar_cast_result FROM VALUES('1970-01-01 00:00:01.2345') AS t(s);

    返回结果如下。

    +--------------------+------------------+---------------------+
    | string_cast_result | char_cast_result | varchar_cast_result |
    +--------------------+------------------+---------------------+
    | 1970-01-01 00:00:01.2345 | 1970-01-01 00:00:01.2345 | 1970-01-01 00:00:01.2345 |
    +--------------------+------------------+---------------------+

函数生成

在MaxCompute和Hive中,FROM_UTC_TIMESTAMP、TO_UTC_TIMESTAMP和CURRENT_TIMESTAMP函数默认返回TIMESTAMP类型的数据。在MaxCompute支持TIMESTAMP_NTZ后,引入了odps.sql.timestamp.function.ntz参数,用于控制函数返回值的数据类型。若该参数取值为true时,返回TIMESTAMP_NTZ类型,否则返回TIMESTAMP类型。使用示例如下:

--开启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命令观察上述查询的执行计划。

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