全部產品
Search

搜尋本產品

    ：MaxCompute TIMESTAMP_NTZ資料類型

    文件中心

    ：MaxCompute TIMESTAMP_NTZ資料類型

    更新時間：Jun 19, 2024

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