Snowflake技术问询：如何通过本地/UTC时间及坐标生成带时区偏移的时间戳

一、能否通过坐标或两个时间戳推断时区？ #

1. 通过坐标推断 #

可以通过经纬度坐标映射到IANA标准时区（如America/New_York），地理坐标与时区存在明确的对应关系，大部分区域的经纬度能唯一匹配到一个IANA时区。但要注意时区分界线附近的坐标，可能需要更精确的地理数据来确认归属时区。

2. 通过LOCAL_TS和UTC_TS推断 #

仅靠一对LOCAL_TS和UTC_TS只能得到当前的时区偏移值（即本地时间与UTC的分钟差），无法直接推断出完整的时区ID。原因是不同时区可能在某个时间点有相同的偏移（比如多个时区在非夏令时期间都使用UTC-5），且不同时区的夏令时切换规则存在差异。如果结合DST_OFFSET_MINUTES字段，能辅助判断当前是否处于夏令时，但仍无法确定具体的时区规则。如果需要获取包含完整夏令时规则的时区，通过坐标映射IANA时区更可靠。

二、生成带时区偏移的时间字符串 #

目标格式2020-11-02T17:00:00-0500是本地时间（ISO格式）+ 时区偏移（HHMM格式），以下是适配你字段类型的实现方案（以Snowflake SQL为例）：

直接通过LOCAL_TS和UTC_TS的差值计算偏移（已包含夏令时影响），拼接成目标格式：

SELECT
  TO_CHAR(LOCAL_TS, 'YYYY-MM-DD"T"HH24:MI:SS') || 
  CASE
    WHEN DATEDIFF(MINUTE, UTC_TS, LOCAL_TS) >= 0 THEN '+' || LPAD(FLOOR(DATEDIFF(MINUTE, UTC_TS, LOCAL_TS)/60),2,'0') || LPAD(MOD(DATEDIFF(MINUTE, UTC_TS, LOCAL_TS),60),2,'0')
    ELSE '-' || LPAD(FLOOR(ABS(DATEDIFF(MINUTE, UTC_TS, LOCAL_TS))/60),2,'0') || LPAD(MOD(ABS(DATEDIFF(MINUTE, UTC_TS, LOCAL_TS)),60),2,'0')
  END AS FORMATTED_TIMESTAMP
FROM YOUR_TABLE;

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