TO_TIMESTAMP

Parse a string into a TIMESTAMP, or convert an epoch-seconds number into a TIMESTAMP.

Category: datetimeReturns: TIMESTAMPDialect: Standard

Syntax

TO_TIMESTAMP(str [, fmt])

Description

## Overview Parses a string into a TIMESTAMP using a format pattern. When the format is omitted, the input is expected in ISO 8601 form (yyyy-MM-dd HH:mm:ss with optional fractional seconds and zone offset). TO_TIMESTAMP is also overloaded in some invocations to accept a numeric argument representing seconds since the Unix epoch, returning the corresponding TIMESTAMP WITH TIME ZONE. ## Behavior - Returns a TIMESTAMP (or TIMESTAMP WITH TIME ZONE when the input format includes a zone offset or when a numeric epoch is supplied). - Returns NULL if either argument is NULL. - Raises an error if the string does not match the pattern or produces an invalid timestamp. - Format tokens use Java SimpleDateFormat style (yyyy, MM, dd, HH, mm, ss, SSS). - Date-only strings parse as midnight at the start of the day. - Time-only strings without a date component produce an error. ## Timezone handling - A format that includes a zone token (z, Z, or X) parses the zone offset and produces a TIMESTAMP WITH TIME ZONE. - Without a zone token, the result is a naive TIMESTAMP WITHOUT TIME ZONE interpreted as local time in the session zone. - Use AT TIME ZONE to shift the parsed instant to a specific zone when needed. ## Format specifiers - **yyyy**: four-digit year. - **MM**: two-digit month (01-12). - **dd**: two-digit day-of-month. - **HH**: two-digit hour (00-23, 24-hour clock). - **hh**: two-digit hour (01-12) paired with **a** for AM/PM. - **mm**: two-digit minute. - **ss**: two-digit second. - **SSS**: three-digit milliseconds. - **SSSSSS**: six-digit microseconds. - **z**: zone name or abbreviation (UTC, EST, etc.). - **Z**: numeric zone offset in +HHMM form. - **X**: ISO 8601 zone offset (+HH, +HHMM, +HH:MM). - Single quotes in the pattern denote literal characters; 'T' can be embedded as ''T'' or 'T'. ## Compatibility - Java SimpleDateFormat pattern family. The default format follows ISO 8601.

Parameters

Name	Type	Description
`str`		Specifies the string to parse as a timestamp. When a numeric epoch argument is supplied in engines that overload the function, it is interpreted as seconds since 1970-01-01 UTC.
`fmt`		Specifies the format pattern describing the input string. When omitted, an ISO 8601 timestamp form (yyyy-MM-dd HH:mm:ss with optional fractional seconds and zone) is assumed.

Examples

-- ISO 8601 timestamp string
SELECT TO_TIMESTAMP('2025-03-15 14:30:00') AS ts;

-- Custom day-first format with time
SELECT TO_TIMESTAMP('15/03/2025 14:30', 'dd/MM/yyyy HH:mm') AS ts;

-- Date-only string becomes midnight
SELECT TO_TIMESTAMP('2025-03-15', 'yyyy-MM-dd') AS ts_at_midnight;

-- ISO 8601 with embedded T separator
SELECT TO_TIMESTAMP(ts_str, 'yyyy-MM-dd''T''HH:mm:ss') AS parsed_ts
FROM staging.events.raw_feed;

-- Convert epoch seconds to a timestamp
SELECT TO_TIMESTAMP(1735689600) AS jan_1_2025_utc;

-- Fractional seconds
SELECT TO_TIMESTAMP('2025-03-15 14:30:45.123', 'yyyy-MM-dd HH:mm:ss.SSS') AS ts_with_ms;

Pitfalls

Format tokens are Java SimpleDateFormat style (HH for hour, mm for minute, ss for second). Note that MM is month and mm is minute; swapping them is a classic bug that silently succeeds for dates where both components are in the 1-12 range.
HH (0-23) and hh (1-12) are distinct. hh without an AM/PM token cannot resolve 14:00 and will error or misparse.
A format without a zone token produces a naive TIMESTAMP. If the source string actually represents UTC, wrap with AT TIME ZONE 'UTC' explicitly.
Fractional seconds need enough S tokens to accommodate the input: 'ss.SSS' accepts three-digit milliseconds; 'ss.SSSSSS' accepts microseconds. Too few tokens truncate silently.
When TO_TIMESTAMP is called with a numeric argument, the number is interpreted as seconds (not milliseconds). Multiplying or dividing by 1000 may be required at the call site depending on the source.