如何将只读链接表中的时间字段拆分为StartTime、EndTime、TimeZone三列？

针对你提到的时间字段格式（如09:00 am - 10:00 am PST、0800 am - 10:00 am EST），可以通过正则提取+格式转换的方式拆分出三个目标字段。以下是基于常见SQL方言的实现方案：

Snowflake 实现 #

假设你的字段名为time_range，表名为supplier_table：

SELECT
  -- 提取并格式化开始时间
  FORMAT(
    PARSE_TIME('hh:mm tt', REGEXP_REPLACE(REGEXP_EXTRACT(time_range, '^(.*?) -'), '^(\\d{2})(\\d{2})', '$1:$2')),
    'h:mm'
  ) AS StartTime,
  -- 提取并格式化结束时间
  FORMAT(
    PARSE_TIME('hh:mm tt', REGEXP_REPLACE(REGEXP_EXTRACT(time_range, ' - (.*?) '), '^(\\d{2})(\\d{2})', '$1:$2')),
    'h:mm'
  ) AS EndTime,
  -- 提取时区
  REGEXP_EXTRACT(time_range, ' ([A-Z]{3})$') AS TimeZone
FROM supplier_table;

步骤说明： #

1. 提取时间片段：用REGEXP_EXTRACT分别截取出开始时间（到-之前）、结束时间（-之后到时区之前）的原始字符串。
2. 统一时间格式：用REGEXP_REPLACE把不带冒号的时间（如0800 am）替换为带冒号的格式（08:00 am），确保后续能正确解析。
3. 解析并格式化：用PARSE_TIME将字符串转为时间类型，再通过FORMAT输出为你需要的h:mm格式（如9:00、10:00）。
4. 提取时区：匹配字段末尾的3位大写字母（如PST、EST）。

MySQL 实现 #

如果使用MySQL，语法调整如下：

SELECT
  DATE_FORMAT(
    STR_TO_DATE(REGEXP_REPLACE(REGEXP_SUBSTR(time_range, '^.*? -'), '^([0-9]{2})([0-9]{2})', '\\1:\\2'), '%h:%i %p'),
    '%h:%i'
  ) AS StartTime,
  DATE_FORMAT(
    STR_TO_DATE(REGEXP_REPLACE(REGEXP_SUBSTR(time_range, ' - .*? '), '^ - ([0-9]{2})([0-9]{2})', '\\1:\\2'), '%h:%i %p'),
    '%h:%i'
  ) AS EndTime,
  TRIM(REGEXP_SUBSTR(time_range, ' [A-Z]{3}$')) AS TimeZone
FROM supplier_table;

关键差异： #

• 用REGEXP_SUBSTR替代Snowflake的REGEXP_EXTRACT
• 用STR_TO_DATE解析时间，DATE_FORMAT格式化输出
• 用TRIM去除时区前的多余空格

注意事项 #

• 如果你的SQL方言不支持正则函数，可以用字符串拆分函数（如SPLIT_PART、STRING_SPLIT）结合位置索引实现，但正则方式更灵活适配两种时间格式。
• 若存在非标准时区格式（不是3位大写字母），需要调整正则表达式匹配规则。

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