MaxCompute：Tipe data TIMESTAMP_NTZ

Informasi latar belakang

Saat mendefinisikan tabel yang berisi bidang tipe TIMESTAMP dalam edisi tipe data MaxCompute V2.0, bidang tersebut mencatat offset waktu dari epoch (UTC 1970-01-01 00:00:00). Data tipe TIMESTAMP tidak menyimpan informasi zona waktu secara internal, tetapi hasil yang ditampilkan dapat dipengaruhi oleh pengaturan zona waktu sistem saat kueri atau tampilan dilakukan. Dalam hal ini, nilai waktu yang sebenarnya dikonversi berdasarkan perbedaan zona waktu. Untuk informasi lebih lanjut tentang zona waktu yang didukung oleh MaxCompute, lihat Zona Waktu.

Sebagai contoh, nilai bidang tipe TIMESTAMP ditampilkan sebagai 1970-01-01 00:00:00 dalam UTC+8. Jika zona waktu beralih ke UTC, nilainya akan ditampilkan sebagai 1969-12-31 16:00:00. Kode berikut menunjukkan sebuah contoh.

1. Aktifkan edisi tipe data MaxCompute V2.0 dan konfirmasi zona waktu.

   -- Aktifkan edisi tipe data MaxCompute V2.0.
   SET odps.sql.type.system.odps2=true;
   -- Secara default, zona waktu proyek MaxCompute adalah UTC+8 (Asia/Shanghai). Jalankan perintah berikut untuk mengembalikan pengaturan properti odps.sql.timezone dan konfirmasi zona waktu saat ini.
   setproject;
   -- Jika zona waktu saat ini proyek bukan UTC+8, jalankan perintah berikut:
   SET odps.sql.timezone=Asia/Shanghai;

2. Buat tabel bernama ts_test yang berisi bidang tipe TIMESTAMP dan kueri data tabel.

   -- Buat tabel untuk menguji perilaku tipe TIMESTAMP.
   CREATE TABLE ts_test(ts timestamp) lifecycle 1;
   -- Masukkan catatan data ke dalam tabel.
   INSERT INTO TABLE ts_test VALUES(timestamp '1970-01-01 00:00:00');
   -- Kueri data tabel.
   SELECT * FROM ts_test;

   Hasil berikut dikembalikan:

   -- Output:
   +------+
   | ts   |
   +------+
   | 1970-01-01 00:00:00 |
   +------+

3. Ubah zona waktu saat ini dan kueri data.

   -- Ubah zona waktu saat ini menjadi UTC.
   SET odps.sql.timezone=UTC;
   
   -- Kueri data tabel.
   SELECT * FROM ts_test;

   Hasil berikut dikembalikan:

   -- Output:
   +------+
   | ts   |
   +------+
   | 1969-12-31 16:00:00 |
   +------+

Perilaku sebelumnya dalam MaxCompute mirip dengan perilaku dalam Hive 2. Namun, perilaku tipe TIMESTAMP dalam Hive 3 tidak bergantung pada pengaturan zona waktu saat ini. Data tipe TIMESTAMP dalam Hive 3 sesuai dengan SQL:2003 atau standar tingkat lanjut. Kode sampel berikut menunjukkan perilaku dalam Hive 3.

-- Atur zona waktu ke UTC+8.
SET time zone Asia/Shanghai;

-- Buat tabel untuk pengujian.
CREATE TABLE ts_test(a timestamp);

-- Masukkan data ke dalam tabel.
INSERT INTO TABLE ts_test VALUES(timestamp '1970-01-01 00:00:00');

-- Baca data dari tabel.
SELECT * FROM ts_test;
-- Hasil berikut dikembalikan:
1970-01-01 00:00:00

-- Gunakan fungsi CAST untuk menguji konversi dari nilai tipe BIGINT menjadi tipe TIMESTAMP.
SELECT cast(0L AS timestamp);
-- Hasil berikut dikembalikan:
1970-01-01 00:00:00

-- Ubah zona waktu menjadi UTC.
SET time zone UTC;

-- Baca data dari tabel lagi.
SELECT * FROM ts_test;
-- Hasil berikut dikembalikan:
1970-01-01 00:00:00

-- Gunakan fungsi CAST untuk menguji konversi dari nilai tipe BIGINT menjadi tipe TIMESTAMP dalam zona waktu baru.
SELECT cast(0L AS timestamp);
-- Hasil berikut dikembalikan:
1970-01-01 00:00:00

MaxCompute adalah sistem komersial yang harus kompatibel dengan Hive 2. Oleh karena itu, perilaku tipe TIMESTAMP yang ada tidak dapat diubah. Namun, untuk mempertimbangkan kompatibilitas dengan Hive 3 dan kepatuhan terhadap standar SQL, tipe TIMESTAMP_NTZ diperkenalkan.

Batasan

• Hologres tidak dapat membaca atau menulis data dari tipe TIMESTAMP_NTZ.

• Anda tidak dapat melakukan operasi baca dan tulis pada data tipe TIMESTAMP_NTZ menggunakan AlgoTask atau pekerjaan PS yang dimulai oleh Platform untuk AI (PAI).

• Jika Anda menggunakan klien MaxCompute (odpscmd) untuk terhubung ke MaxCompute, pastikan versi klien adalah V0.46 atau lebih baru.

Konfigurasikan tipe data bidang sebagai TIMESTAMP_NTZ

Setelah mengaktifkan edisi tipe data MaxCompute V2.0, Anda dapat membuat tabel yang berisi bidang tipe TIMESTAMP_NTZ. Bagian ini memberikan contoh.

1. Aktifkan edisi tipe data MaxCompute V2.0 dan konfirmasi zona waktu.

   -- Aktifkan edisi tipe data MaxCompute V2.0.
   SET odps.sql.type.system.odps2=true;
   -- Secara default, zona waktu proyek MaxCompute adalah UTC+8 (Asia/Shanghai). Jalankan perintah berikut untuk mengembalikan pengaturan properti odps.sql.timezone dan konfirmasi zona waktu saat ini.
   setproject;
   -- Jika zona waktu saat ini proyek bukan UTC+8, jalankan perintah berikut: 
   SET odps.sql.timezone=Asia/Shanghai;

2. Buat tabel bernama ts_test01 dan kueri data tabel.

   -- Buat tabel yang berisi dua bidang. Satu bidang adalah tipe TIMESTAMP, dan bidang lainnya adalah tipe TIMESTAMP_NTZ. Kedua bidang digunakan untuk membandingkan perbedaan perilaku antara kedua tipe data.
   CREATE TABLE ts_test02(a timestamp, b timestamp_ntz);
   -- Masukkan data ke dalam tabel.
   INSERT INTO TABLE ts_test02 VALUES(timestamp '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00');
   
   -- Kueri data tabel.
   SELECT * FROM ts_test02;

   Hasil berikut dikembalikan:

   -- Output
   +------+------+
   | a    | b    |
   +------+------+
   | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
   +------+------+

3. Ubah zona waktu saat ini dan kueri data.

   -- Ubah zona waktu saat ini menjadi UTC.
   SET odps.sql.timezone=UTC;
   -- Kueri data tabel lagi.
   SELECT * FROM ts_test02;

   Hasil berikut dikembalikan:

   -- Tipe data Bidang a adalah TIMESTAMP, dan hasil tampilannya telah berubah. Tipe data Bidang b adalah TIMESTAMP_NTZ, dan hasil tampilannya tetap tidak berubah.
   +------+------+
   | a    | b    |
   +------+------+
   | 1969-12-31 16:00:00 | 1970-01-01 00:00:00 |
   +------+------+

Hasilkan data dari tipe TIMESTAMP_NTZ

-- Aktifkan edisi tipe data MaxCompute V2.0.
SET odps.sql.type.system.odps2=true;

-- Setel parameter odps.sql.timestamp.function.ntz menjadi true.
SET odps.sql.timestamp.function.ntz=true;
-- Panggil ketiga fungsi tersebut.
SELECT current_timestamp() AS current_result, from_utc_timestamp(0L, 'UTC') AS from_result, to_utc_timestamp(0L, 'UTC') as to_result;

-- Hasil berikut dikembalikan. Perhatikan bahwa hasil eksekusi fungsi CURRENT_TIMESTAMP didasarkan pada waktu sistem saat ini.
+----------------+-------------+-----------+
| 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;

-- Potongan kode berikut menunjukkan bahwa bidang current_result, from_result, dan to_result dalam output adalah dari tipe TIMESTAMP_NTZ.
            FS: output: Screen
                schema:
                  current_result (timestamp_ntz)
                  from_result (timestamp_ntz)
                  to_result (timestamp_ntz)

Operasi yang didukung

Operasi relasional

Tipe TIMESTAMP_NTZ MaxCompute mendukung operator relasional berikut. Untuk informasi lebih lanjut tentang operator relasional, lihat Operator Relasional.

• Sama dengan (=), Tidak Sama dengan (!=), dan Eqns (<=>)

  -- Aktifkan edisi tipe data MaxCompute V2.0.
  SET odps.sql.type.system.odps2=true;
  
  -- Gunakan operator Equals, Not Equals, dan 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);

  Hasil berikut dikembalikan:

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

• GT (>), GE (>=), LT (<), dan LE (<=)

  -- Aktifkan edisi tipe data 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);

  Hasil berikut dikembalikan:

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

Operasi aritmatika

• Kurangi satu nilai tipe TIMESTAMP_NTZ dengan nilai lain tipe TIMESTAMP_NTZ. Hasil pengurangan adalah tipe INTERVAL_DAY_TIME.

  -- Aktifkan edisi tipe data MaxCompute V2.0.
  SET odps.sql.type.system.odps2=true;
  -- Kurangi satu nilai dari tipe TIMESTAMP_NTZ dengan nilai lain dari tipe TIMESTAMP_NTZ.
  SELECT timestamp_ntz '1970-01-01 00:01:30' - timestamp_ntz '1970-01-01 00:00:00';

  Hasil berikut dikembalikan:

  Output:
  +------+
  | _c0  |
  +------+
  | 0 00:01:30.000000000 |
  +------+

• Tambahkan atau kurangi nilai tipe INTERVAL_YEAR_MONTH ke atau dari nilai tipe TIMESTAMP_NTZ.

  -- Aktifkan edisi tipe data MaxCompute V2.0.
  SET odps.sql.type.system.odps2=true;
  -- Tambahkan atau kurangi nilai dari tipe INTERVAL_YEAR_MONTH ke atau dari nilai dari tipe 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);

  Hasil berikut dikembalikan:

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

• Tambahkan atau kurangi nilai tipe INTERVAL_DAY_TIME ke atau dari nilai tipe TIMESTAMP_NTZ.

  -- Aktifkan edisi tipe data MaxCompute V2.0.
  SET odps.sql.type.system.odps2=true;
  -- Tambahkan atau kurangi nilai dari tipe INTERVAL_DAY_TIME ke atau dari nilai dari tipe 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);

  Hasil berikut dikembalikan:

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

Fungsi tanggal dan waktu

Untuk fungsi tanggal dan waktu, parameter input dapat berupa tipe TIMESTAMP atau TIMESTAMP_NTZ. Untuk informasi lebih lanjut tentang fungsi tanggal dan waktu, lihat Fungsi Tanggal.

• Contoh 1: Fungsi DATEADD

  -- Aktifkan edisi tipe data 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);

  Hasil berikut dikembalikan:

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

• Contoh 2: Fungsi MONTH

  -- Aktifkan edisi tipe data 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);

  Hasil berikut dikembalikan:

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

Fungsi agregat

Fungsi agregat MAX dan MIN mendukung tipe TIMESTAMP_NTZ. Kode berikut menunjukkan sebuah contoh:

-- Aktifkan edisi tipe data 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);

Hasil berikut dikembalikan:

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

UDFs

Kelas fungsi yang ditentukan pengguna Java (UDF) java.time.LocalDateTime mendukung parameter input dan output tipe TIMESTAMP_NTZ. Untuk demonstrasi yang lebih baik, UDF tertanam digunakan dalam contoh ini. Untuk informasi lebih lanjut tentang UDF tertanam, lihat UDF Tertanam.

Anda harus mengirimkan dan mengeksekusi pernyataan SQL dalam mode skrip. Untuk informasi lebih lanjut tentang mode skrip, lihat SQL dalam Mode Skrip.

-- Aktifkan edisi tipe data MaxCompute V2.0.
SET odps.sql.type.system.odps2=true;
-- Definisikan fungsi foo_udf: Tentukan variabel sebagai nilai dari tipe TIMESTAMP_NTZ, ubah bagian milidetik dari nilai menjadi 999, dan biarkan bagian lainnya tetap tidak berubah.
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;
-- Tentukan variabel sebagai 1970-01-01 00:00:00, yang merupakan nilai dari tipe TIMESTAMP_NTZ. Masukkan nilai tersebut ke dalam fungsi foo_udf, dan kueri hasil pemrosesan.
SELECT foo_udf(a) FROM VALUES(timestamp_ntz '1970-01-01 00:00:00') AS t(a);

Hasil berikut dikembalikan:

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