Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 format string 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

UsageDescriptionExample
to_timestamp(string, format)Convert string to timestamp using formatto_timestamp('2024-06-01 15:30:00', 'YYYY-MM-DD HH24:MI:SS')
to_timestamp(number)Convert Unix epoch seconds to timestampto_timestamp(1712125800)
Format patternsUse patterns like YYYY, MM, DD, HH24, MI, SSYYYY-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.