Teradata查询迁移Snowflake时Interval运算写入varchar列报错

• 原有Teradata查询核心逻辑：通过CASE语句拼接时间字符串后转换为Interval hour to minute类型，叠加CASE语句生成的Interval hour类型小时偏移量，最终输出'-04:00'、'20:00'这类varchar类型结果，对应示例SQL如下：

select (case '-' when '-' then '-' ||'04' || ':' ||'00'
else '04' || ':' ||'00'
end (Interval hour to minute)) +
 (case
    '2400' when '2400' then 24
    else 0
end (interval hour));

• 当第二个CASE的判断值为'1835'时，上述SQL输出varchar类型的20:00。

• 迁移目标：从@JOB_MANAGEMENT.SNOWFALKE外部阶段读取input_file.txt文件，解析raw_data字段后通过SUBSTR取值拼接时间字符串、叠加对应小时偏移量，最终写入varchar类型的COLUMN_1字段，初始编写的迁移SQL如下：

SELECT                             
(CASE SUBSTR(raw_data, 48, 1) 
 WHEN '-' THEN CONCAT('-' , SUBSTR(raw_data,49,2) , ':' , SUBSTR(raw_data,51,2))
 ELSE CONCAT(SUBSTR(raw_data,49,2) , ':' , SUBSTR(raw_data,51,2)) END) + 
 (CASE SUBSTR(raw_data,40,4)  WHEN '2400' THEN 24 ELSE 0  END)  
AS COLUMN_1
FROM
    (SELECT  temp_row.$1 as raw_data  from
           @JOB_MANAGEMENT.SNOWFALKE (file_format => 'DB.TBL_FILE_FORMAT',
            pattern=>'.*/input_file.txt') temp_table) temp;

• 用固定示例值简化SQL做测试，测试语句如下：

SELECT                             
(CASE '-' WHEN '-' 
THEN CONCAT('-' , '04' , ':' , '00')
ELSE CONCAT('04' , ':' , '00') END)  + 
(CASE '1825'  WHEN '2400' THEN 24 ELSE 0  END)

• 预期输出为varchar类型的-04:00，但Snowflake抛出错误：

Numeric value '-04:00' is not recognized
语句执行失败，无法得到预期结果。

Snowflake不支持Teradata的隐式类型转换规则：不会自动把HH:MM格式的字符串识别为Interval类型，也不支持时间格式字符串和数值直接做加法运算。执行加法时Snowflake会默认尝试把左侧字符串转为数值，碰到冒号无法识别就会抛出上述错误。

所有类型转换显式声明，Interval计算完成后再格式化回要求的varchar格式：

1. 将拼接得到的带符号时间字符串显式转换为INTERVAL HOUR TO MINUTE类型
2. 将CASE生成的小时偏移量数值显式转换为INTERVAL HOUR类型
3. 两个Interval值完成加法运算后，通过TO_VARCHAR函数格式化为HH24:MI格式的字符串，负号会自动保留，最终输出为varchar类型。

简化测试修正SQL #

SELECT 
  TO_VARCHAR(
    (CASE '-' WHEN '-' 
      THEN CONCAT('-' , '04' , ':' , '00')
      ELSE CONCAT('04' , ':' , '00') 
    END)::INTERVAL HOUR TO MINUTE
    +
    (CASE '1825'  WHEN '2400' THEN 24 ELSE 0  END)::INTERVAL HOUR,
    'HH24:MI'
  ) AS COLUMN_1;

执行后输出varchar类型的-04:00，符合预期。

生产场景完整修正SQL #

SELECT                             
  TO_VARCHAR(
    (CASE SUBSTR(raw_data, 48, 1) 
      WHEN '-' THEN CONCAT('-' , SUBSTR(raw_data,49,2) , ':' , SUBSTR(raw_data,51,2))
      ELSE CONCAT(SUBSTR(raw_data,49,2) , ':' , SUBSTR(raw_data,51,2)) 
    END)::INTERVAL HOUR TO MINUTE
    + 
    (CASE SUBSTR(raw_data,40,4)  WHEN '2400' THEN 24 ELSE 0  END)::INTERVAL HOUR,
    'HH24:MI'
  ) AS COLUMN_1
FROM
(
  SELECT  temp_row.$1 as raw_data  
  FROM @JOB_MANAGEMENT.SNOWFALKE (
    file_format => 'DB.TBL_FILE_FORMAT',
    pattern=>'.*/input_file.txt'
  ) temp_table
) temp;

结果校验 #

• 拼接时间为-04:00、偏移量为0时，输出-04:00
• 拼接时间为-04:00、偏移量为24时，输出20:00
  和原Teradata逻辑计算结果完全一致，可直接写入varchar类型的目标列。

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