Introdução ao tipo de dados TIMESTAMP_NTZ no MaxCompute - MaxCompute: Central de Documentação do Alibaba Cloud

TIMESTAMP_NTZ é um tipo de timestamp sem fuso horário na edição de tipos de dados do MaxCompute V2.0. Ele armazena exatamente a hora local registrada e sempre retorna esse valor inalterado, independentemente do fuso horário da sessão. Isso torna as comparações de tempo e as operações aritméticas previsíveis, sem exigir conversão de fuso horário.

Como funciona

Os tipos TIMESTAMP e TIMESTAMP_NTZ armazenam o tempo de formas diferentes:

	TIMESTAMP	TIMESTAMP_NTZ
Armazenamento interno	Deslocamento UTC a partir da época (1970-01-01 00:00:00 UTC)	Hora local, sem referência de fuso horário
Comportamento de exibição	Ajustado para o fuso horário atual da sessão	Sempre retorna o valor conforme escrito
Conversão de fuso horário	Necessária na leitura	Nenhuma
Padrão SQL	Compatível com Hive 2	Compatível com SQL:2003 / Hive 3

Por exemplo, um valor TIMESTAMP escrito como 1970-01-01 00:00:00 em UTC+8 é exibido como 1969-12-31 16:00:00 quando a sessão muda para UTC. A mesma entrada armazenada como TIMESTAMP_NTZ sempre aparece como 1970-01-01 00:00:00, independentemente do fuso horário da sessão.

O exemplo a seguir demonstra essa diferença. Ative a edição de tipos de dados do MaxCompute V2.0 e defina o fuso horário para UTC+8 (o padrão para projetos do 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;

Crie uma tabela com ambos os tipos e insira o mesmo valor de timestamp:

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

Saída:

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

Altere o fuso horário da sessão para UTC e consulte novamente:

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

Saída: o campo a (TIMESTAMP) sofre um deslocamento de 8 horas; o campo b (TIMESTAMP_NTZ) permanece inalterado:

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

Pré-requisitos

Antes de começar, verifique se você tem:

A edição de tipos de dados do MaxCompute V2.0 ativada (SET odps.sql.type.system.odps2=true)
(Para exemplos de UDF) Instruções SQL enviadas no modo script — consulte SQL no modo script

Limitações

Hologres: Não é possível ler ou gravar dados TIMESTAMP_NTZ.
Platform for AI (PAI): Jobs AlgoTask e PS não leem nem gravam dados TIMESTAMP_NTZ.
Cliente MaxCompute (odpscmd): Requer a versão 0,46 ou posterior.

Usar TIMESTAMP_NTZ em uma tabela

Após ativar a edição de tipos de dados do MaxCompute V2.0, use timestamp_ntz como o tipo da coluna em 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;

Saída:

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

Gerar valores TIMESTAMP_NTZ

Literais

Use a palavra-chave TIMESTAMP_NTZ seguida por uma string de data e hora:

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

Conversão de tipo com CAST

Use a função CAST para converter outros tipos em TIMESTAMP_NTZ. Todos os exemplos exigem a edição de tipos de dados do MaxCompute V2.0.

Tipos de tempo para 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;

Saída:

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

Tipos numéricos para 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;

Saída:

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

Tipos de string para 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);

Saída:

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

Valores de retorno de funções

As funções FROM_UTC_TIMESTAMP, TO_UTC_TIMESTAMP e CURRENT_TIMESTAMP retornam TIMESTAMP por padrão. Defina odps.sql.timestamp.function.ntz=true para que elas retornem 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;

Saída (o valor de current_result reflete a hora real do sistema no momento da execução):

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

Para confirmar os tipos de retorno, execute EXPLAIN na consulta:

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

O plano de execução mostra todos os três campos de saída como timestamp_ntz:

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

Operações compatíveis

Operadores relacionais

O tipo TIMESTAMP_NTZ aceita todos os operadores relacionais padrão. Para obter mais informações, consulte Operadores.

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

Operações aritméticas

Para obter mais informações, consulte Operadores.

Subtrair dois valores TIMESTAMP_NTZ — o resultado é 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

Adicionar ou subtrair 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

Adicionar ou subtrair 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

Funções de data e hora

As funções de data e hora aceitam tanto TIMESTAMP quanto TIMESTAMP_NTZ como entrada. Para a referência completa de funções, consulte Funções de data.

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

Funções de agregação

MAX e MIN aceitam 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);

Saída:

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

UDFs

Funções definidas pelo usuário (UDFs) Java usam java.time.LocalDateTime para mapear parâmetros de entrada e saída TIMESTAMP_NTZ.

Envie o código a seguir como um job SQL no modo script. Para obter mais informações, consulte UDFs com código incorporado e SQL no modo script.

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

Saída:

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

Próximos passos

Fusos horários — fusos horários compatíveis com o MaxCompute
Funções de data — referência completa de funções de data e hora
Operadores — referência de operadores relacionais
Operadores — referência de operadores aritméticos
Função CAST — referência de conversão de tipos
UDFs com código incorporado — guia de criação de UDFs