MaxCompute:MaxCompute における TIMESTAMP_NTZ データ型の概要

背景情報

MaxCompute V2.0 データ型エディションで TIMESTAMP 型のフィールドを含むテーブルを定義すると、そのようなフィールドはエポック（UTC 1970-01-01 00:00:00）からの時間オフセットを記録します。内部ストレージの観点からは、TIMESTAMP 型のデータにはタイムゾーン情報は含まれていません。ただし、表示される結果は、クエリまたは表示中にシステムで使用される現在のタイムゾーン設定の影響を受ける可能性があります。この場合、実際に表示される時刻値は、タイムゾーンの差に基づいて変換されます。 MaxCompute でサポートされているタイムゾーンの詳細については、「タイムゾーン」をご参照ください。

たとえば、TIMESTAMP 型のフィールドの値は、UTC + 08:00 では 1970-01-01 00:00:00 と表示されます。現在のタイムゾーンを UTC に切り替えると、値は 1969-12-31 16:00:00 と表示されます。次のコードは例を示しています。

1. MaxCompute V2.0 データ型エディションを有効にして、タイムゾーンを確認します。

   -- MaxCompute V2.0 データ型エディションを有効にします。
   SET odps.sql.type.system.odps2=true;
   -- デフォルトでは、MaxCompute プロジェクトのタイムゾーンは UTC + 08:00（Asia/Shanghai）です。次のコマンドを実行して、odps.sql.timezone プロパティの設定を返し、現在のタイムゾーンを確認します。
   setproject;
   -- プロジェクトの現在のタイムゾーンが UTC + 08:00 でない場合は、次のコマンドを実行します。
   SET odps.sql.timezone=Asia/Shanghai;

2. ts_test という名前の TIMESTAMP 型のフィールドを含むテーブルを作成し、テーブルデータのクエリを実行します。

   -- 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 の動作を示しています。

-- タイムゾーンを UTC + 08:00 に設定します。
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

-- CAST 関数を使用して、BIGINT 型の値から 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

-- CAST 関数を使用して、新しいタイムゾーンで BIGINT 型の値から TIMESTAMP 型への変換をテストします。
SELECT cast(0L AS timestamp);
-- 次の結果が返されます。
1970-01-01 00:00:00

MaxCompute は商用システムであり、Hive 2 と互換性がある必要があります。 MaxCompute の TIMESTAMP 型の既存の動作を変更することはできません。また、Hive 3 の動作との互換性と SQL 標準への準拠も考慮する必要があります。そのため、これらの目的のために TIMESTAMP_NTZ 型が追加されています。

制限事項

• Hologres は、TIMESTAMP_NTZ 型のデータを読み書きできません。

• Platform for AI（PAI）によって開始された AlgoTask または PS ジョブを使用して、TIMESTAMP_NTZ 型のデータに対する読み取りおよび書き込み操作を実行することはできません。

• MaxCompute クライアント（odpscmd）を使用して MaxCompute に接続する場合は、クライアントバージョンが V0.46 以降であることを確認する必要があります。

フィールドデータ型を TIMESTAMP_NTZ として構成する

MaxCompute V2.0 データ型エディションを有効にすると、TIMESTAMP_NTZ 型のフィールドを含むテーブルを作成できます。このセクションでは例を示します。

1. MaxCompute V2.0 データ型エディションを有効にして、タイムゾーンを確認します。

   -- MaxCompute V2.0 データ型エディションを有効にします。
   SET odps.sql.type.system.odps2=true;
   -- デフォルトでは、MaxCompute プロジェクトのタイムゾーンは UTC + 08:00（Asia/Shanghai）です。次のコマンドを実行して、odps.sql.timezone プロパティの設定を返し、現在のタイムゾーンを確認します。
   setproject;
   -- プロジェクトの現在のタイムゾーンが UTC + 08:00 でない場合は、次のコマンドを実行します。
   SET odps.sql.timezone=Asia/Shanghai;

2. ts_test01 という名前のテーブルを作成し、テーブルデータのクエリを実行します。

   -- 2 つのフィールドを含むテーブルを作成します。1 つのフィールドは TIMESTAMP 型で、もう 1 つのフィールドは TIMESTAMP_NTZ 型です。2 つのフィールドは、2 つのデータ型間の動作の違いを比較するために使用されます。
   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 型のデータを生成する

-- MaxCompute V2.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 フィールドが TIMESTAMP_NTZ 型であることを示しています。
            FS: output: Screen
                schema:
                  current_result (timestamp_ntz)
                  from_result (timestamp_ntz)
                  to_result (timestamp_ntz)

サポートされている操作

関係演算

MaxCompute の TIMESTAMP_NTZ 型は、次の関係演算子をサポートしています。関係演算子の詳細については、「関係演算子」をご参照ください。

• 等しい（=）、等しくない（!=）、および Eqns（<=>）

  -- MaxCompute V2.0 データ型エディションを有効にします。
  SET odps.sql.type.system.odps2=true;
  
  -- 等しい、等しくない、および 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（<=）

  -- MaxCompute V2.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 型の値から別の TIMESTAMP_NTZ 型の値を減算します。減算結果は INTERVAL_DAY_TIME 型です。

  -- MaxCompute V2.0 データ型エディションを有効にします。
  SET odps.sql.type.system.odps2=true;
  -- TIMESTAMP_NTZ 型の値から別の 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 |
  +------+

• INTERVAL_YEAR_MONTH 型の値を TIMESTAMP_NTZ 型の値に加算または減算します。

  -- MaxCompute V2.0 データ型エディションを有効にします。
  SET odps.sql.type.system.odps2=true;
  -- INTERVAL_YEAR_MONTH 型の値を TIMESTAMP_NTZ 型の値に加算または減算します。
  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 |
  +-------------+--------------+

• INTERVAL_DAY_TIME 型の値を TIMESTAMP_NTZ 型の値に加算または減算します。

  -- MaxCompute V2.0 データ型エディションを有効にします。
  SET odps.sql.type.system.odps2=true;
  -- INTERVAL_DAY_TIME 型の値を TIMESTAMP_NTZ 型の値に加算または減算します。
  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 関数

  -- MaxCompute V2.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 関数

  -- MaxCompute V2.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        |
  +----------+----------+

集計関数

MAX 集計関数と MIN 集計関数は、TIMESTAMP_NTZ 型をサポートしています。次のコードは例を示しています。

-- MaxCompute V2.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 の詳細については、「コード埋め込み UDF」をご参照ください。

スクリプトモードで SQL 文を送信して実行する必要があります。スクリプトモードの詳細については、「スクリプトモードの SQL」をご参照ください。

-- MaxCompute V2.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;
-- 変数を 1970-01-01 00:00:00.123456789 に設定し、foo_udf 関数を呼び出します。
SELECT foo_udf(timestamp_ntz '1970-01-01 00:00:00.123456789');
-- 次の結果が返されます。
+-----------------------+
| foo_udf(c0)          |
+-----------------------+
| 1970-01-01 00:00:00.999 |
+-----------------------+

次の結果が返されます。

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