SQLite中自定义格式日期转Unix时间戳问题求解（无需编程语言）

SQLite的strftime()函数只支持有限的几种标准日期格式（比如ISO8601、RFC3339等），你遇到的Fri Dec 01 18:54:58 GMT+02:00 2017这种格式不在默认支持列表里，所以直接调用会返回NULL。我们可以通过字符串拆分+日期函数组合的方式手动转换，完全用SQLite自身功能实现，不需要外部编程语言。

核心思路 #

1. 从原始日期字符串中拆分出年、月、日、时间和时区偏移量
2. 将月份缩写转换为数字格式（比如Dec→12）
3. 组合成SQLite能识别的ISO格式日期字符串
4. 根据时区偏移量转换为UTC时间
5. 用strftime()生成Unix时间戳（秒级），再乘以1000得到毫秒级（和你原来的需求一致）

分步实现（先测试再更新） #

首先建议先执行查询验证转换结果是否正确：

SELECT
    createDate AS original_date,
    strftime('%s', 
        datetime(
            -- 组合成ISO格式日期：YYYY-MM-DD HH:MM:SS
            substr(createDate, instr(createDate, ' ', -1)+1) || '-' || -- 提取年份
            CASE substr(createDate, 5, 3)
                WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03'
                WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
                WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09'
                WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' WHEN 'Dec' THEN '12'
            END || '-' || -- 月份缩写转数字
            substr(createDate, 9, 2) || ' ' || -- 提取日期
            substr(createDate, 12, 8), -- 提取时间
            -- 根据时区偏移转换为UTC时间：原时间是GMT+offset，UTC=原时间-offset小时
            '-' || COALESCE(CAST(substr(createDate, instr(createDate, 'GMT')+3, 3) AS INTEGER), 0) || ' hours'
        )
    ) * 1000 AS unix_timestamp_ms
FROM Table
WHERE id=1;

如果查询结果符合预期，再执行更新语句：

UPDATE Table
SET createDate = strftime('%s', 
        datetime(
            substr(createDate, instr(createDate, ' ', -1)+1) || '-' ||
            CASE substr(createDate, 5, 3)
                WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03'
                WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
                WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09'
                WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' WHEN 'Dec' THEN '12'
            END || '-' ||
            substr(createDate, 9, 2) || ' ' ||
            substr(createDate, 12, 8),
            '-' || COALESCE(CAST(substr(createDate, instr(createDate, 'GMT')+3, 3) AS INTEGER), 0) || ' hours'
        )
    ) * 1000
WHERE id=1;

注意事项 #

• 确保所有原始日期字符串的格式完全一致（比如月份都是3字母缩写、日期是两位数字、时区格式为GMT±xx:00），如果有格式不一致的情况，需要调整字符串截取的位置或添加额外的处理逻辑（比如用trim()去除多余空格）
• COALESCE()函数用于处理时区为GMT（无偏移量）的情况，默认偏移为0
• 建议先备份数据再执行更新操作，避免意外错误

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