Snowflake中如何将Timestamp转换为浮点型日期序列号

要在Snowflake中实现类似SQL Server里convert(float, my_timestamp_field)的效果，得到包含时间部分的浮点型日期序列号（整数部分为自1900-01-01起的天数，小数部分为当天时间占一天的比例），可以通过以下方法实现：

核心思路 #

SQL Server的浮点日期序列号本质是目标时间与1900-01-01的时间差（以天为单位，包含小数部分）。在Snowflake中，我们可以先计算两个时间点的总秒数差，再除以一天的总秒数（86400）来得到浮点结果。

实现代码 #

假设你的时间字段为my_timestamp_field（建议使用TIMESTAMP_NTZ类型以匹配SQL Server无时区的datetime逻辑），执行以下SQL：

SELECT
  DATEDIFF(SECOND, '1900-01-01'::TIMESTAMP_NTZ, my_timestamp_field) / 86400.0 AS float_serial_number
FROM your_table;

精度说明 #

• 如果需要更高精度（比如毫秒级），可以将SECOND替换为MILLISECOND，并除以86400000.0：

  SELECT
    DATEDIFF(MILLISECOND, '1900-01-01'::TIMESTAMP_NTZ, my_timestamp_field) / 86400000.0 AS float_serial_number
  FROM your_table;
  

• 示例中的2022-02-07 12:57:45.000通过上述语句计算后，会得到44597.5401041667，与SQL Server的输出完全一致。

注意事项 #

• 确保你的时间字段与'1900-01-01'的时区类型一致：如果字段是带时区的TIMESTAMP_TZ，则需要将基准时间转换为对应时区（如'1900-01-01'::TIMESTAMP_TZ），避免时区转换导致的误差。

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