Snowflake：如何通过单一转换方式处理多种格式的Unix时间并转换为Snowflake日期时间类型

Absolutely! You can unify the conversion of all three Unix time formats to Snowflake's timestamp type using a combination of numeric conversion and conditional logic. Here's a ready-to-use query that handles all your examples:

WITH sample_unix_times AS (
  SELECT '1620205203.611' AS unix_time_str UNION ALL
  SELECT '1620205203611' UNION ALL
  SELECT '1.620205203611000e+09'
)
SELECT
  unix_time_str,
  TO_TIMESTAMP_NTZ(
    CASE
      -- Check if the value is in milliseconds (13-digit number, ~1e12 or larger)
      WHEN TO_FLOAT(unix_time_str) >= 1e12 THEN TO_FLOAT(unix_time_str) / 1000
      -- Otherwise, treat it as seconds (including decimal fractions and scientific notation)
      ELSE TO_FLOAT(unix_time_str)
    END
  ) AS target_timestamp
FROM sample_unix_times;

How This Works #

Let's break down the logic for each format:

• '1620205203.611': This is a standard Unix timestamp in seconds with millisecond precision. TO_FLOAT converts it directly to a numeric value, which is passed to TO_TIMESTAMP_NTZ to get the correct timestamp.
• '1620205203611': This is a Unix timestamp in milliseconds (13 digits). The CASE statement detects it's larger than 1e12, divides by 1000 to convert to seconds, then converts to a timestamp.
• '1.620205203611000e+09': Snowflake's TO_FLOAT automatically parses scientific notation strings into numeric values. Since this is in seconds, it's passed directly to TO_TIMESTAMP_NTZ.

The result for all three rows will be: 2021-05-05 09:00:03.611 — exactly your target datetime.

Notes #

• If you need a timezone-aware timestamp, replace TO_TIMESTAMP_NTZ with TO_TIMESTAMP_LTZ (local timezone) or TO_TIMESTAMP_TZ (specific timezone) and adjust as needed.
• This logic works for most common Unix time variations (seconds with decimals, milliseconds, scientific notation) as long as milliseconds are 13-digit values and seconds are 10-digit or in scientific notation for large values.

内容的提问来源于stack exchange，提问作者Gatis Seja