Converts a DATETIME, TIMESTAMP, or TIMESTAMP_NTZ value to a UNIX timestamp in milliseconds. The return value is a BIGINT representing the number of milliseconds elapsed since 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970. Fractional seconds beyond millisecond precision are truncated, not rounded.
Syntax
BIGINT TO_MILLIS(DATETIME|TIMESTAMP|TIMESTAMP_NTZ <date>);
-- Returns 1617174900000.
SELECT TO_MILLIS(DATETIME '2021-03-31 15:15:00');Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
date | Yes | DATETIME, TIMESTAMP, or TIMESTAMP_NTZ | The date and time value to convert. |
Return value
Returns a BIGINT representing the number of milliseconds from 00:00:00 UTC on January 1, 1970, to date.
| Condition | Result |
|---|---|
date is not a DATETIME, TIMESTAMP, or TIMESTAMP_NTZ value | Error |
date is NULL and data type edition 1.0 is in use | NULL |
date is NULL and data type edition 2.0 is in use | Error |
Examples
Example 1: Convert a DATETIME value
-- Returns 1743405300000.
SELECT TO_MILLIS(DATETIME '2025-03-31 15:15:00');Example 2: Convert a TIMESTAMP value
TIMESTAMP supports sub-second precision. TO_MILLIS retains only the first three digits of the fractional seconds and discards the rest.
-- 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');
-- Fractional seconds beyond milliseconds are truncated, not rounded.
-- Returns 1736524800123.
SELECT TO_MILLIS(TIMESTAMP '2025-01-11 00:00:00.123456789');Example 3: Convert a TIMESTAMP_NTZ value
TIMESTAMP_NTZ stores a date and time value with no time zone information.
-- 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
The behavior depends on the active data type edition.
-- 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;
-- Returns an error.
SELECT TO_MILLIS(NULL);Related functions
TO_MILLIS is a date function. For other date calculation and conversion functions, see Date functions.