在PostgreSQL中将带小数的Excel日期字符串转换为时间戳

核心原理 #

Excel的日期存储逻辑是：以1899-12-30为基准日，整数部分代表基准日之后的天数，小数部分代表当天时间占全天的比例（例如0.5对应12:00:00，0.25对应06:00:00）。你之前的方案用Round取整丢失了小数部分的时间信息，只需保留完整浮点数并转换为时间间隔即可。

完整转换SQL语句 #

SELECT 
    Excel_date_number,
    ('1899-12-30'::TIMESTAMP + (REPLACE(Excel_date_number, ',', '.')::DOUBLE PRECISION || ' days')::INTERVAL) AS full_timestamp
FROM 
    your_table_name;

语句拆解 #

• REPLACE(Excel_date_number, ',', '.')::DOUBLE PRECISION：将带逗号的字符串替换为点号，转换为浮点数，保留完整的日期+时间数值
• (数值 || ' days')::INTERVAL：将浮点数转换为PostgreSQL的时间间隔类型，小数部分会自动换算为小时、分钟、秒
• '1899-12-30'::TIMESTAMP + 时间间隔：基准时间加上时间间隔，得到完整的时间戳

示例验证 #

针对你提供的示例数据：

• 输入45279,4029282407 → 转换后得到2023-12-19 09:40:13左右的时间戳
• 输入45294,5203472222 → 转换后得到2024-01-04 12:29:20左右的时间戳
• 输入45309,2083333333 → 转换后得到2024-01-18 05:00:00（刚好是0.208333*24=5小时）

额外优化（可选） #

如果需要格式化输出时间戳，可以用TO_CHAR函数：

SELECT 
    Excel_date_number,
    TO_CHAR(
        ('1899-12-30'::TIMESTAMP + (REPLACE(Excel_date_number, ',', '.')::DOUBLE PRECISION || ' days')::INTERVAL),
        'YYYY-MM-DD HH24:MI:SS'
    ) AS formatted_timestamp
FROM 
    your_table_name;

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