Databricks と Snowflake にて TIMESTAMP 型と TIMESTAMP_NTZ 型を文字型に変換するの動作差異

概要

Databricks と Snowflake では、TIMESTAMP 型および TIMESTAMP_NTZ 型を文字列型に変換する際の桁数に動作差異があるようです。

検証

Databricks

%sql
WITH src AS (
  SELECT
    TIMESTAMP_NTZ'2024-01-01 00:00:00' AS timestamp_ntz,
    TIMESTAMP'2024-01-01 00:00:00'     AS timestamp
)
SELECT
  CAST(timestamp_ntz AS STRING) AS timestamp_ntz_string,
  CAST(timestamp AS STRING) AS timestamp_string
FROM src

timestamp_ntz_string	timestamp_string
2024-01-01 00:00:00	2024-01-01 00:00:00

Snowflake

WITH src AS (
  SELECT
    '2024-01-01 00:00:00'::TIMESTAMP_NTZ AS timestamp_ntz,
    '2024-01-01 00:00:00'::TIMESTAMP     AS timestamp_value
)
SELECT
  timestamp_ntz::STRING AS timestamp_ntz_string,
  timestamp_value::STRING AS timestamp_string
FROM src;

TIMESTAMP_NTZ_STRING	TIMESTAMP_STRING
2024-01-01 00:00:00.000	2024-01-01 00:00:00.000