How to Use to_timestamp in Snowflake: Syntax and Examples
In Snowflake, use the
to_timestamp function to convert a string or number into a timestamp value. You provide the input and optionally a format string to match the input pattern. This helps convert dates and times stored as text or numbers into proper timestamp data types.Syntax
The to_timestamp function converts input values to a timestamp. It has two main forms:
to_timestamp(string, format): Converts a string to timestamp using the given format.to_timestamp(number): Converts a Unix epoch number (seconds since 1970-01-01 00:00:00 UTC) to timestamp.
The format is optional and uses date/time patterns like YYYY-MM-DD HH24:MI:SS.
sql
to_timestamp(string_or_number, [format])
Example
This example shows how to convert a string with a date and time into a timestamp, and how to convert a Unix epoch number into a timestamp.
sql
SELECT to_timestamp('2024-06-01 15:30:00', 'YYYY-MM-DD HH24:MI:SS') AS ts_from_string, to_timestamp(1712125800) AS ts_from_epoch;
Output
TS_FROM_STRING | TS_FROM_EPOCH
------------------------|------------------------
2024-06-01 15:30:00.000 | 2024-04-02 15:30:00.000
Common Pitfalls
Common mistakes when using to_timestamp include:
- Not matching the
formatstring exactly to the input string, causing errors or wrong results. - Passing a string without a format when the default format does not match.
- Confusing Unix epoch seconds with milliseconds (Snowflake expects seconds).
Always verify your input format and data type before conversion.
sql
/* Wrong: format does not match input string */ SELECT to_timestamp('06/01/2024 15:30', 'YYYY-MM-DD HH24:MI:SS'); /* Right: matching format string */ SELECT to_timestamp('06/01/2024 15:30', 'MM/DD/YYYY HH24:MI');
Quick Reference
| Usage | Description | Example |
|---|---|---|
| to_timestamp(string, format) | Convert string to timestamp using format | to_timestamp('2024-06-01 15:30:00', 'YYYY-MM-DD HH24:MI:SS') |
| to_timestamp(number) | Convert Unix epoch seconds to timestamp | to_timestamp(1712125800) |
| Format patterns | Use patterns like YYYY, MM, DD, HH24, MI, SS | YYYY-MM-DD HH24:MI:SS |
Key Takeaways
Use to_timestamp to convert strings or numbers into timestamp values in Snowflake.
Always provide a matching format string when converting from strings to avoid errors.
Unix epoch numbers must be in seconds, not milliseconds, for correct conversion.
Check your input data format carefully before using to_timestamp.
to_timestamp helps handle date/time data stored as text or numbers for querying.
