Skip to main content
Version: Candidate-4.0

to_datetime

Converts a Unix timestamp to a DATETIME type value based on the current time zone settings.

For detailed instructions on setting a time zone, see Configure a time zone.

If you want to convert a Unix timestamp to a DATETIME type value independent of the current session's time zone settings, you can use to_datetime_ntz.

Syntax​

DATETIME to_datetime(BIGINT unix_ts, INT scale)

Parameters​

NameTypeRequiredDescription
unix_tsBIGINTYesThe Unix timestamp to convert. For example, 1598306400 (seconds) and 1598306400123 (milliseconds).
scaleINTNoTime precision. Valid values:
  • 0 indicates seconds (Default).
  • 3 indicates milliseconds.
  • 6 indicates microseconds.

Return Value​

  • On success: returns a DATETIME value based on the current session's time zone.
  • On failure: returns NULL. Common reasons include:
    • Invalid scale (not 0, 3, or 6)
    • Value out of DATETIME range (0001-01-01 to 9999-12-31)

Example​

SET time_zone = 'Asia/Shanghai';

SELECT to_datetime(1598306400);
-- Returns: 2020-08-25 06:00:00

SELECT to_datetime(1598306400123, 3);
-- Returns: 2020-08-25 06:00:00.123000