Redshift插入数据时如何从BIGINT时间戳提取年月日

你需要先将BIGINT类型的时间戳转换为Redshift支持的日期时间类型（如TIMESTAMP），因为EXTRACT函数仅能作用于日期时间类数据，无法直接处理数值型的时间戳。

关键说明 #

你的示例时间戳1543258003796是毫秒级（13位），转换时需要注意时间单位的匹配，以下是两种可行的实现方式：

方式1：使用TIMESTAMP 'epoch' + 时间间隔转换

直接基于纪元时间（1970-01-01 00:00:00）加上毫秒间隔生成TIMESTAMP，再用TO_CHAR格式化输出符合目标表的字符串格式：

INSERT INTO time_table (month_date, year_date)
SELECT 
    -- 输出格式为YYYY-MM（如2018-11），可根据需求调整格式符
    TO_CHAR(TIMESTAMP 'epoch' + ts.timestamp * INTERVAL '1 millisecond', 'YYYY-MM') AS month_date,
    -- 输出格式为4位年份（如2018）
    TO_CHAR(TIMESTAMP 'epoch' + ts.timestamp * INTERVAL '1 millisecond', 'YYYY') AS year_date
FROM ts_table ts;

方式2：使用TO_TIMESTAMP函数转换

TO_TIMESTAMP默认接收秒级时间戳，因此需要将毫秒值除以1000，再格式化输出：

INSERT INTO time_table (month_date, year_date)
SELECT 
    TO_CHAR(TO_TIMESTAMP(ts.timestamp / 1000), 'YYYY-MM') AS month_date,
    TO_CHAR(TO_TIMESTAMP(ts.timestamp / 1000), 'YYYY') AS year_date
FROM ts_table ts;

可选：用EXTRACT提取后转字符串 #

如果仅需要提取纯数字的月份/年份（如月份为'11'、年份为'2018'），可以先转换为TIMESTAMP，再用EXTRACT提取后转为字符串：

INSERT INTO time_table (month_date, year_date)
SELECT 
    CAST(EXTRACT(MONTH FROM TIMESTAMP 'epoch' + ts.timestamp * INTERVAL '1 millisecond') AS VARCHAR(20)),
    CAST(EXTRACT(YEAR FROM TIMESTAMP 'epoch' + ts.timestamp * INTERVAL '1 millisecond') AS VARCHAR(20))
FROM ts_table ts;

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