Introduction to the TIMESTAMP_NTZ data type in MaxCompute - MaxCompute

TIMESTAMP_NTZ est un type d'horodatage indépendant du fuseau horaire disponible dans l'édition des types de données V2.0 de MaxCompute. Il stocke l'heure locale exacte saisie et renvoie toujours cette valeur sans modification, quel que soit le fuseau horaire de la session. Cette caractéristique rend les comparaisons et les calculs temporels prévisibles, sans nécessiter de conversion de fuseau horaire.

Fonctionnement

Les types TIMESTAMP et TIMESTAMP_NTZ stockent les informations temporelles différemment :

	TIMESTAMP	TIMESTAMP_NTZ
Stockage interne	Décalage UTC par rapport à l'époque (1970-01-01 00:00:00 UTC)	Heure locale, sans référence au fuseau horaire
Comportement d'affichage	Ajusté selon le fuseau horaire de la session actuelle	Renvoie toujours la valeur telle qu'elle a été saisie
Conversion de fuseau horaire	Requise lors de la lecture	Aucune
Norme SQL	Compatible avec Hive 2	Compatible avec SQL:2003 / Hive 3

Par exemple, une valeur TIMESTAMP saisie sous la forme 1970-01-01 00:00:00 en UTC+8 s'affiche sous la forme 1969-12-31 16:00:00 lorsque la session passe en UTC. La même entrée stockée en tant que TIMESTAMP_NTZ s'affiche toujours sous la forme 1970-01-01 00:00:00, indépendamment du fuseau horaire de la session.

L'exemple suivant illustre cette différence. Activez l'édition des types de données V2.0 de MaxCompute et définissez le fuseau horaire sur UTC+8 (valeur par défaut des projets MaxCompute) :

-- Enable the MaxCompute V2.0 data type edition
SET odps.sql.type.system.odps2=true;
-- Confirm the current time zone (default: Asia/Shanghai, which is UTC+8)
setproject;
-- If your project time zone is not UTC+8, set it explicitly
SET odps.sql.timezone=Asia/Shanghai;

Créez une table contenant les deux types et insérez la même valeur d'horodatage :

-- Create a table with both types for comparison
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');

-- Query results in UTC+8
SELECT * FROM ts_test02;

Résultat :

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

Basculez le fuseau horaire de la session vers UTC et exécutez à nouveau la requête :

SET odps.sql.timezone=UTC;
SELECT * FROM ts_test02;

Résultat : le champ a (TIMESTAMP) subit un décalage de 8 heures ; le champ b (TIMESTAMP_NTZ) reste inchangé :

+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 1969-12-31 16:00:00 | 1970-01-01 00:00:00 |
+---------------------+---------------------+

Prérequis

Avant de commencer, assurez-vous d'avoir :

Activé l'édition des types de données V2.0 de MaxCompute (SET odps.sql.type.system.odps2=true)
(Pour les exemples UDF) Soumis les instructions SQL en mode script — voir SQL in script mode

Limitations

Hologres : Impossible de lire ou d'écrire des données TIMESTAMP_NTZ.
Platform for AI (PAI) : Les tâches AlgoTask et PS ne peuvent ni lire ni écrire des données TIMESTAMP_NTZ.
Client MaxCompute (odpscmd) : Version 0.46 ou ultérieure requise.

Utiliser TIMESTAMP_NTZ dans une table

Après avoir activé l'édition des types de données V2.0 de MaxCompute, utilisez timestamp_ntz comme type de colonne dans l'instruction CREATE TABLE :

SET odps.sql.type.system.odps2=true;
CREATE TABLE ts_test01(ts timestamp_ntz) lifecycle 1;
INSERT INTO TABLE ts_test01 VALUES(timestamp_ntz '1970-01-01 00:00:00');
SELECT * FROM ts_test01;

Résultat :

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

Générer des valeurs TIMESTAMP_NTZ

Littéraux

Utilisez le mot-clé TIMESTAMP_NTZ suivi d'une chaîne de date et d'heure :

-- Returns 2017-11-11 00:00:00.123456789
SELECT TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789';

Conversion de type avec CAST

Utilisez la fonction CAST pour convertir d'autres types en TIMESTAMP_NTZ. Tous les exemples nécessitent l'édition des types de données V2.0 de MaxCompute.

Types temporels vers TIMESTAMP_NTZ

SET odps.sql.type.system.odps2=true;
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;

Résultat :

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

Types numériques vers TIMESTAMP_NTZ

SET odps.sql.type.system.odps2=true;
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;

Résultat :

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

Types chaîne vers TIMESTAMP_NTZ

SET odps.sql.type.system.odps2=true;
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);

Résultat :

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

Valeurs de retour des fonctions

Les fonctions FROM_UTC_TIMESTAMP, TO_UTC_TIMESTAMP et CURRENT_TIMESTAMP renvoient un type TIMESTAMP par défaut. Définissez odps.sql.timestamp.function.ntz=true pour qu'elles renvoient plutôt un type TIMESTAMP_NTZ :

SET odps.sql.type.system.odps2=true;
SET odps.sql.timestamp.function.ntz=true;

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

Résultat (la valeur current_result reflète l'heure système réelle au moment de l'exécution) :

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

Pour confirmer les types de retour, exécutez EXPLAIN sur la requête :

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

Le plan d'exécution indique que les trois champs de sortie sont de type timestamp_ntz :

 FS: output: Screen
     schema:
       current_result (timestamp_ntz)
       from_result (timestamp_ntz)
       to_result (timestamp_ntz)

Opérations prises en charge

Opérateurs relationnels

TIMESTAMP_NTZ prend en charge tous les opérateurs relationnels standards. Pour plus d'informations, consultez la section Operators.

SET odps.sql.type.system.odps2=true;

-- Equals (=), Not Equals (!=), and 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);
-- Output: true | false | true

-- GT (>), GE (>=), LT (<), LE (<=)
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);
-- Output: false | true | false | true

Opérations arithmétiques

Pour plus d'informations, consultez la section Operators.

Soustraire deux valeurs TIMESTAMP_NTZ : le résultat est de type INTERVAL_DAY_TIME :

SET odps.sql.type.system.odps2=true;
SELECT timestamp_ntz '1970-01-01 00:01:30' - timestamp_ntz '1970-01-01 00:00:00';
-- Output: 0 00:01:30.000000000

Ajouter ou soustraire INTERVAL_YEAR_MONTH :

SET odps.sql.type.system.odps2=true;
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);
-- Output: plus_result=1971-01-01 00:00:00  |  minus_result=1969-01-01 00:00:00

Ajouter ou soustraire INTERVAL_DAY_TIME :

SET odps.sql.type.system.odps2=true;
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);
-- Output: plus_result=1970-01-02 00:00:00  |  minus_result=1969-12-31 00:00:00

Fonctions de date et d'heure

Les fonctions de date et d'heure acceptent à la fois TIMESTAMP et TIMESTAMP_NTZ en entrée. Pour la référence complète des fonctions, consultez la section Date functions.

SET odps.sql.type.system.odps2=true;

-- DATEADD: add 1 day to both types
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);
-- Output: a_result=1970-01-02 00:00:00  |  b_result=1970-01-02 00:00:00

-- MONTH: extract the month from both types
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);
-- Output: a_result=1  |  b_result=1

Fonctions d'agrégation

MAX et MIN prennent en charge TIMESTAMP_NTZ :

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);

Résultat :

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

UDF

Les fonctions définies par l'utilisateur (UDF) Java utilisent java.time.LocalDateTime pour mapper les paramètres d'entrée et de sortie TIMESTAMP_NTZ.

Soumettez le code suivant en tant que tâche SQL en mode script. Pour plus d'informations, consultez les sections Code-embedded UDFs et SQL in script mode.

SET odps.sql.type.system.odps2=true;

-- Define a UDF that sets the millisecond component to 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;

-- Pass a TIMESTAMP_NTZ value to the UDF
SELECT foo_udf(a) FROM VALUES(timestamp_ntz '1970-01-01 00:00:00') AS t(a);

Résultat :

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

Étapes suivantes

Time zones — fuseaux horaires pris en charge dans MaxCompute
Date functions — référence complète des fonctions de date et d'heure
Operators — référence des opérateurs relationnels
Operators — référence des opérateurs arithmétiques
CAST function — référence de la conversion de type
Code-embedded UDFs — guide de création d'UDF