MaxCompute:TIMESTAMP_NTZ data type

Relational operators

TIMESTAMP_NTZ supports all standard relational operators. For more information, see Relational 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

Arithmetic operations

For more information, see Operators.

Subtract two TIMESTAMP_NTZ values — the result is 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

Add or subtract 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

Add or subtract 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

Date and time functions

Date and time functions accept both TIMESTAMP and TIMESTAMP_NTZ as input. For the full function reference, see 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

Aggregate functions

MAX and MIN support 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);

Output:

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

UDFs

Java user-defined functions (UDFs) use java.time.LocalDateTime to map TIMESTAMP_NTZ input and output parameters.

Submit the following as a script-mode SQL job. For more information, see Code-embedded UDFs and 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);

Output:

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