Redshift中不借助Timestamp的SQL UDF实现字符串转Epoch整数

以下是完全基于SQL编写的UDF，不依赖任何与Timestamp类型互转的内置函数，通过手动计算自1970-01-01以来的毫秒数实现需求：

CREATE OR REPLACE FUNCTION str_to_epoch_ms(time_str VARCHAR)
RETURNS BIGINT
IMMUTABLE
AS $$
DECLARE
    year INT;
    month INT;
    day INT;
    hour INT;
    minute INT;
    sec INT;
    ms INT;
    leap_days_before INT;
    days_in_year INT;
    total_days BIGINT;
    total_seconds BIGINT;
BEGIN
    -- 从输入字符串提取时间各部分
    year := CAST(SUBSTRING(time_str, 1, 4) AS INT);
    month := CAST(SUBSTRING(time_str, 6, 2) AS INT);
    day := CAST(SUBSTRING(time_str, 9, 2) AS INT);
    hour := CAST(SUBSTRING(time_str, 12, 2) AS INT);
    minute := CAST(SUBSTRING(time_str, 15, 2) AS INT);
    sec := CAST(SUBSTRING(time_str, 18, 2) AS INT);
    ms := CAST(CASE WHEN POSITION('.' IN time_str) > 0 THEN SUBSTRING(time_str, POSITION('.' IN time_str)+1, 3) ELSE '000' END AS INT);

    -- 计算1970年到目标年份前一年的总闰年数
    leap_days_before := 
        FLOOR((year - 1) / 4) - FLOOR((year - 1) / 100) + FLOOR((year - 1) / 400)
        - (FLOOR(1969 / 4) - FLOOR(1969 / 100) + FLOOR(1969 / 400));

    -- 计算目标年份到目标月份前的总天数
    days_in_year := CASE
        WHEN month > 1 THEN 31 ELSE 0 END +
        CASE
            WHEN month > 2 THEN (CASE WHEN (year % 4 = 0 AND year % 100 != 0) OR year % 400 = 0 THEN 29 ELSE 28 END) ELSE 0 END +
        CASE
            WHEN month > 3 THEN 31 ELSE 0 END +
        CASE
            WHEN month > 4 THEN 30 ELSE 0 END +
        CASE
            WHEN month > 5 THEN 31 ELSE 0 END +
        CASE
            WHEN month > 6 THEN 30 ELSE 0 END +
        CASE
            WHEN month > 7 THEN 31 ELSE 0 END +
        CASE
            WHEN month > 8 THEN 31 ELSE 0 END +
        CASE
            WHEN month > 9 THEN 30 ELSE 0 END +
        CASE
            WHEN month > 10 THEN 31 ELSE 0 END +
        CASE
            WHEN month > 11 THEN 30 ELSE 0 END;

    -- 总天数：1970到前一年的总天数 + 当年已过天数 + 当月天数-1（因为从0点开始算）
    total_days := (year - 1970) * 365 + leap_days_before + days_in_year + (day - 1);

    -- 转换为秒数，加上时分秒，再转毫秒
    total_seconds := total_days * 86400 + hour * 3600 + minute * 60 + sec;
    RETURN total_seconds * 1000 + ms;
END;
$$ LANGUAGE plpgsql;

关键逻辑说明 #

• 时间拆分：通过SUBSTRING和POSITION函数精准提取年、月、日、时、分、秒、毫秒部分，兼容不带毫秒的输入（默认补000）。
• 闰年计算：采用标准闰年规则（能被4整除但不能被100整除，或能被400整除），计算1970年到目标年份前一年的总闰年数，确保天数计算准确。
• 月份天数累加：通过CASE语句逐个判断月份，动态处理闰年2月的天数差异。
• 毫秒转换：最终将总秒数乘以1000，加上提取的毫秒值，得到完整的Epoch毫秒数。

测试示例 #

SELECT str_to_epoch_ms('2017-08-18 11:59:30.345');
-- 输出：1503057570345

注意事项 #

• 输入字符串必须严格遵循YYYY-MM-DD HH:MI:SS.FFF格式，否则会导致提取值错误。
• 仅支持1970年及以后的时间（Epoch起始年份），早于1970的时间会返回负数结果。

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