在Amazon Redshift中将混合格式时长转换为小时

针对包含1 day 07:00:00和2 days这类混合格式的时间字段，可使用以下Amazon Redshift查询将其转换为总小时数：

SELECT
    CASE
        WHEN POSITION('day', estimated_time_to_pickup) > 0 THEN
            24 * CAST(SPLIT_PART(estimated_time_to_pickup, ' ', 1) AS INTEGER) +
            EXTRACT(HOUR FROM TO_TIMESTAMP(SUBSTRING(estimated_time_to_pickup, POSITION('day', estimated_time_to_pickup) + 3), 'HH24:MI:SS'))
        ELSE
            24 * CAST(TRIM(' days' FROM estimated_time_to_pickup)::INTEGER)
    END AS estimated_time_in_hours
FROM 
    "dev"."staging"."stg_zidship__service_levels_servicelevel"
LIMIT 100;

逻辑说明： #

• 处理X day HH:MM:SS格式：
  1. 用SPLIT_PART提取天数部分，转换为整数后乘以24，得到天数对应的小时数；
  2. 截取day之后的时间字符串，通过TO_TIMESTAMP转换为时间类型，再用EXTRACT提取小时数，最后与天数小时数相加得到总时长。
• 处理X days格式：
  使用TRIM去掉字段末尾的 days后缀，转换为整数后乘以24，得到总小时数。

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