The TIMESTAMP_NTZ data type is added to the MaxCompute V2.0 data type edition. TIMESTAMP_NTZ is a data type for dates and time points without a time zone. Data of the TIMESTAMP_NTZ type is based on a uniform time standard, such as Coordinated Universal Time (UTC). Compared with data of the TIMESTAMP type, data of the TIMESTAMP_NTZ type does not require time zone conversion. Therefore, data comparison and operations are more convenient.
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.
Enable the MaxCompute V2.0 data type edition and confirm the time zone.
SET odps.sql.type.system.odps2=true;
setproject;
SET odps.sql.timezone=Asia/Shanghai;
Create a table named ts_test
that contains a field of the TIMESTAMP type and query table data.
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;
The following result is returned:
+
| ts |
+
| 1970-01-01 00:00:00 |
+
Change the current time zone and query data.
SET odps.sql.timezone=UTC;
SELECT * FROM ts_test;
The following result is returned:
+
| 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 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
SELECT cast(0L AS timestamp);
1970-01-01 00:00:00
SET time zone UTC;
SELECT * FROM ts_test;
1970-01-01 00:00:00
SELECT cast(0L AS timestamp);
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.
Enable the MaxCompute V2.0 data type edition and confirm the time zone.
SET odps.sql.type.system.odps2=true;
setproject;
SET odps.sql.timezone=Asia/Shanghai;
Create a table named ts_test01
and query table data.
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;
The following result is returned:
+
| a | b |
+
| 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
+
Change the current time zone and query data.
SET odps.sql.timezone=UTC;
SELECT * FROM ts_test02;
The following result is returned:
+
| a | b |
+
| 1969-12-31 16:00:00 | 1970-01-01 00:00:00 |
+
Generate data of the TIMESTAMP_NTZ type
TIMESTAMP_NTZ constant
Convert a data type into TIMESTAMP_NTZ
Generate a function
Syntax
TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789'
Example
SELECT TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789';
MaxCompute allows you to use the CAST function to convert a data type into TIMESTAMP_NTZ.
Example 1: Convert various time types into 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;
The following result is returned:
+
| 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 |
+
Example 2: Convert numeric types into 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;
The following result is returned:
+
| 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 |
+
Example 3: Convert character types into 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);
The following result is returned:
+
| 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 |
+
In MaxCompute and Hive, the FROM_UTC_TIMESTAMP, TO_UTC_TIMESTAMP, and CURRENT_TIMESTAMP functions return data of the TIMESTAMP type by default. After the TIMESTAMP_NTZ type is added to MaxCompute, the odps.sql.timestamp.function.ntz
parameter is introduced to control the data type of the return value of a function. If this parameter is set to true, a value of the TIMESTAMP_NTZ type is returned. Otherwise, a value of the TIMESTAMP type is returned. The following code shows an example:
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;
+
| 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 |
+
Execute the EXPLAIN statement to view the execution plan of the preceding query statement.
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 result is returned:
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 (<=>)
SET odps.sql.type.system.odps2=true;
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 (<=)
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.
SET odps.sql.type.system.odps2=true;
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.
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);
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.
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);
The following result is returned:
+
| plus_result | minus_result |
+
| 1970-01-02 00:00:00 | 1969-12-31 00:00:00 |
+
Note
For more information about arithmetic operators, see Operators.
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
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
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:
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.
SET odps.sql.type.system.odps2=true;
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;
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 |
+