MaxCompute:TIMESTAMP_NTZ data type in MaxCompute

Background information

When you define a table that contains fields of the TIMESTAMP type in the MaxCompute V2.0 data type edition, such fields record the time offsets from the epoch (UTC 1970-01-01 00:00:00). Data of the TIMESTAMP type does not contain time zone information in terms of internal storage. However, the displayed results may be subject to the current time zone settings used by the system during the query or display. In this case, the actually displayed time value is converted based on the time zone difference. For more information about the time zones that are supported by MaxCompute, see Time zones.

For example, the value of a field of the TIMESTAMP type is displayed as 1970-01-01 00:00:00 in UTC+8. If the current time zone is switched to UTC, the value is displayed as 1969-12-31 16:00:00. The following code shows an example.

1. Enable the MaxCompute V2.0 data type edition and confirm the time zone.

   -- Enable the MaxCompute V2.0 data type edition.
   SET odps.sql.type.system.odps2=true;
   -- By default, the time zone of a MaxCompute project is UTC+8 (Asia/Shanghai). Run the following command to return the setting of the odps.sql.timezone property and confirm the current time zone.
   setproject;
   -- If the current time zone of the project is not UTC+8, run the following command:
   SET odps.sql.timezone=Asia/Shanghai;

2. Create a table named ts_test that contains a field of the TIMESTAMP type and query table data.

   -- Create a table to test the behavior of the TIMESTAMP type.
   CREATE TABLE ts_test(ts timestamp) lifecycle 1;
   -- Insert a data record into the table.
   INSERT INTO TABLE ts_test VALUES(timestamp '1970-01-01 00:00:00');
   -- Query table data.
   SELECT * FROM ts_test;

   The following result is returned:

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

3. Change the current time zone and query data.

   -- Change the current time zone to UTC.
   SET odps.sql.timezone=UTC;
   
   -- Query table data.
   SELECT * FROM ts_test;

   The following result is returned:

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

The preceding behavior in MaxCompute is the same as the behavior in Hive 2. However, the behavior of the TIMESTAMP type in Hive 3 does not depend on the current time zone settings. Data of the TIMESTAMP type in Hive 3 conforms to SQL:2003 or advanced standards. The following sample code shows the behavior in Hive 3.

-- Set the time zone to UTC+8.
SET time zone Asia/Shanghai;

-- Create a table for testing.
CREATE TABLE ts_test(a timestamp);

-- Insert data into the table.
INSERT INTO TABLE ts_test VALUES(timestamp '1970-01-01 00:00:00');

-- Read data from the table.
SELECT * FROM ts_test;
-- The following result is returned:
1970-01-01 00:00:00

-- Use the CAST function to test the conversion from a value of the BIGINT type into the TIMESTAMP type.
SELECT cast(0L AS timestamp);
-- The following result is returned:
1970-01-01 00:00:00

-- Change the time zone to UTC.
SET time zone UTC;

-- Read data from the table again.
SELECT * FROM ts_test;
-- The following result is returned:
1970-01-01 00:00:00

-- Use the CAST function to test the conversion from a value of the BIGINT type into the TIMESTAMP type in the new time zone.
SELECT cast(0L AS timestamp);
-- The following result is returned:
1970-01-01 00:00:00

MaxCompute is a commercial system and needs to be compatible with Hive 2. You cannot modify the existing behavior of the TIMESTAMP type in MaxCompute. You also need to consider compatibility with the behavior in Hive 3 and compliance with SQL standards. Therefore, the TIMESTAMP_NTZ type is added for these purposes.

Limits

• Hologres cannot read or write data of the TIMESTAMP_NTZ type.

• You cannot perform read and write operations on data of the TIMESTAMP_NTZ type by using AlgoTask or PS jobs initiated by Platform for AI (PAI).

• If you use the MaxCompute client (odpscmd) to connect to MaxCompute, you must make sure that the client version is V0.46 or later.

Configure the field data type as TIMESTAMP_NTZ

After you enable the MaxCompute V2.0 data type edition, you can create a table that contains fields of the TIMESTAMP_NTZ type. This section provides an example.

1. Enable the MaxCompute V2.0 data type edition and confirm the time zone.

   -- Enable the MaxCompute V2.0 data type edition.
   SET odps.sql.type.system.odps2=true;
   -- By default, the time zone of a MaxCompute project is UTC+8 (Asia/Shanghai). Run the following command to return the setting of the odps.sql.timezone property and confirm the current time zone.
   setproject;
   -- If the current time zone of the project is not UTC+8, run the following command: 
   SET odps.sql.timezone=Asia/Shanghai;

2. Create a table named ts_test01 and query table data.

   -- Create a table that contains two fields. One field is of the TIMESTAMP type, and the other field is of the TIMESTAMP_NTZ type. The two fields are used to compare the behavior differences between the two data types.
   CREATE TABLE ts_test02(a timestamp, b timestamp_ntz);
   -- Insert data into the table.
   INSERT INTO TABLE ts_test02 VALUES(timestamp '1970-01-01 00:00:00', timestamp_ntz '1970-01-01 00:00:00');
   
   -- Query table data.
   SELECT * FROM ts_test02;

   The following result is returned:

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

3. Change the current time zone and query data.

   -- Change the current time zone to UTC.
   SET odps.sql.timezone=UTC;
   -- Query table data again.
   SELECT * FROM ts_test02;

   The following result is returned:

   -- The data type of Field a is TIMESTAMP, and its display result has changed. The data type of Field b is TIMESTAMP_NTZ, and its display result remains unchanged.
   +------+------+
   | a    | b    |
   +------+------+
   | 1969-12-31 16:00:00 | 1970-01-01 00:00:00 |
   +------+------+

Generate data of the TIMESTAMP_NTZ type

-- Enable the MaxCompute V2.0 data type edition.
SET odps.sql.type.system.odps2=true;

-- Set the odps.sql.timestamp.function.ntz parameter to true.
SET odps.sql.timestamp.function.ntz=true;
-- Call the three functions.
SELECT current_timestamp() AS current_result, from_utc_timestamp(0L, 'UTC') AS from_result, to_utc_timestamp(0L, 'UTC') as to_result;

-- The following result is returned. Note that the execution result of the CURRENT_TIMESTAMP function is based on the current system time.
+----------------+-------------+-----------+
| 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;

-- The following code snippet indicates that the current_result, from_result, and to_result fields in the output are of the TIMESTAMP_NTZ type.
            FS: output: Screen
                schema:
                  current_result (timestamp_ntz)
                  from_result (timestamp_ntz)
                  to_result (timestamp_ntz)

Supported operations

Relational operations

The TIMESTAMP_NTZ type of MaxCompute supports the following relational operators. For more information about relational operators, see Relational operators.

• Equals (=), Not Equals (!=), and Eqns (<=>)

  -- Enable the MaxCompute V2.0 data type edition.
  SET odps.sql.type.system.odps2=true;
  
  -- Use the Equals, Not Equals, and Eqns operators.
  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);

  The following result is returned:

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

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

  -- Enable the MaxCompute V2.0 data type edition.
  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);

  The following result is returned:

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

Arithmetic operations

• Subtract one value of the TIMESTAMP_NTZ type from another value of the TIMESTAMP_NTZ type. The subtraction result is of the INTERVAL_DAY_TIME type.

  -- Enable the MaxCompute V2.0 data type edition.
  SET odps.sql.type.system.odps2=true;
  -- Subtract one value of the TIMESTAMP_NTZ type from another value of the TIMESTAMP_NTZ type.
  SELECT timestamp_ntz '1970-01-01 00:01:30' - timestamp_ntz '1970-01-01 00:00:00';

  The following result is returned:

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

• Add or subtract a value of the INTERVAL_YEAR_MONTH type to or from a value of the TIMESTAMP_NTZ type.

  -- Enable the MaxCompute V2.0 data type edition.
  SET odps.sql.type.system.odps2=true;
  -- Add or subtract a value of the INTERVAL_YEAR_MONTH type to or from a value of the TIMESTAMP_NTZ type.
  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);

  The following result is returned:

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

• Add or subtract a value of the INTERVAL_DAY_TIME type to or from a value of the TIMESTAMP_NTZ type.

  -- Enable the MaxCompute V2.0 data type edition.
  SET odps.sql.type.system.odps2=true;
  -- Add or subtract a value of the INTERVAL_DAY_TIME type to or from a value of the TIMESTAMP_NTZ type.
  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);

  The following result is returned:

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

Date and time functions

For date and time functions, input parameters can be of the TIMESTAMP or TIMESTAMP_NTZ type. For more information about date and time functions, see Date functions.

• Example 1: DATEADD function

  -- Enable the MaxCompute V2.0 data type edition.
  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);

  The following result is returned:

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

• Example 2: MONTH function

  -- Enable the MaxCompute V2.0 data type edition.
  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);

  The following result is returned:

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

Aggregate functions

The MAX and MIN aggregate functions support the TIMESTAMP_NTZ type. The following code shows an example:

-- Enable the MaxCompute V2.0 data type edition.
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);

The following result is returned:

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

UDFs

The Java user-defined function (UDF) class java.time.LocalDateTime supports input and output parameters of the TIMESTAMP_NTZ type. For better demonstration, a code-embedded UDF is used in this example. For more information about code-embedded UDFs, see Code-embedded UDFs.

You must submit and execute SQL statements in script mode. For more information about the script mode, see SQL in script mode.

-- Enable the MaxCompute V2.0 data type edition.
SET odps.sql.type.system.odps2=true;
-- Define the foo_udf function: Specify a variable as a value of the TIMESTAMP_NTZ type, change the millisecond part of the value to 999, and remain other parts unchanged.
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;
-- Specify a variable as 1970-01-01 00:00:00, which is a value of the TIMESTAMP_NTZ type. Pass the value into the foo_udf function, and query the processing result.
SELECT foo_udf(a) FROM VALUES(timestamp_ntz '1970-01-01 00:00:00') AS t(a);

The following result is returned:

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