to_millis command format, parameters, and return value - MaxCompute

The TO_MILLIS function converts a specified date and time value, date, into a UNIX timestamp. The timestamp is a BIGINT value that represents the number of milliseconds since 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970.

Syntax

BIGINT TO_MILLIS(DATETIME|TIMESTAMP|TIMESTAMP_NTZ <date>);

-- Standard example.
-- Returns 1617174900000.
SELECT TO_MILLIS(DATETIME '2021-03-31 15:15:00');

Parameters

date: Required. A date value of the DATETIME, TIMESTAMP, or TIMESTAMP_NTZ type.

Return value

The function returns a BIGINT value that represents the number of milliseconds from 00:00:00 UTC on January 1, 1970, to the specified date. The return value is determined by the following rules:

If date is not a DATETIME, TIMESTAMP, or TIMESTAMP_NTZ type, the function returns an error.
If date is NULL:
- For data type edition 1.0, the function returns NULL.
- For data type edition 2.0, the function returns an error.

Examples

Example 1: Convert a DATETIME value to a UNIX timestamp in milliseconds.

-- Returns 1743405300000.
SELECT TO_MILLIS(DATETIME '2025-03-31 15:15:00');

Example 2: Convert a TIMESTAMP value to a UNIX timestamp in milliseconds.

-- Set the data type edition to 2.0.
SET odps.sql.type.system.odps2 = true;

-- The TIMESTAMP value includes milliseconds.
-- Returns 1736524800123.
SELECT TO_MILLIS(TIMESTAMP '2025-01-11 00:00:00.123');

-- During conversion, the last six digits of the fractional part of the seconds are truncated, and only the first three digits are retained.
-- Returns 1736524800123.
SELECT TO_MILLIS(TIMESTAMP '2025-01-11 00:00:00.123456789');

Example 3: Convert a TIMESTAMP_NTZ value to a UNIX timestamp in milliseconds.

-- Set the data type edition to 2.0.
SET odps.sql.type.system.odps2 = true;
-- Returns 1740817815123.
SELECT TO_MILLIS(TIMESTAMP_NTZ '2025-03-01 08:30:15.123');

Example 4: Handle a NULL input.

-- Set the data type edition to 1.0.
SET odps.sql.type.system.odps2 = false;
-- Returns NULL.
SELECT TO_MILLIS(NULL);

-- Set the data type edition to 2.0.
SET odps.sql.type.system.odps2 = true;
-- An error is returned.
SELECT TO_MILLIS(NULL);

Related functions

TO_MILLIS is a date function. For more information about functions for date calculations and conversions, see Date functions.