如何在SQLite中处理‘YYYY-MM-DD HH:MM:SS.SSS +0000’格式并计算时间差

一、'+0000'的含义 #

'+0000'是UTC时区偏移量，表示该日期时间基于协调世界时（UTC），与UTC的时间差为0小时0分钟，也就是和UTC时间完全同步。因为JULIANDAY()默认不识别带这种时区偏移后缀的日期格式，所以直接传入会返回null。

二、正确计算时间间隔的方法 #

针对SQLite的JULIANDAY()函数，你可以通过以下两种方式处理日期字符串，让函数能正确解析：

方法1：截取字符串去除时区后缀 #

用SUBSTR()函数截取到毫秒部分，去掉后面的 +0000：

SELECT 
  JULIANDAY(SUBSTR(DATE_A, 1, 23)) - JULIANDAY(SUBSTR(DATE_B, 1, 23)) AS day_interval
FROM your_table;

SUBSTR(DATE_A, 1, 23)会把'2022-05-12 00:16:17.553 +0000'截取为'2022-05-12 00:16:17.553'，符合JULIANDAY()的识别格式。

方法2：替换时区后缀为UTC标识 #

部分SQLite版本支持带UTC标记的日期格式，将 +0000替换为 UTC即可：

SELECT 
  JULIANDAY(REPLACE(DATE_A, ' +0000', ' UTC')) - JULIANDAY(REPLACE(DATE_B, ' +0000', ' UTC')) AS day_interval
FROM your_table;

扩展：转换为其他时间单位 #

如果需要得到小时、分钟或秒级的间隔，可以对天数差值进行换算：

• 小时间隔：(JULIANDAY(处理后的DATE_A) - JULIANDAY(处理后的DATE_B)) * 24
• 分钟间隔：(JULIANDAY(处理后的DATE_A) - JULIANDAY(处理后的DATE_B)) * 24 * 60
• 秒间隔：(JULIANDAY(处理后的DATE_A) - JULIANDAY(处理后的DATE_B)) * 24 * 60 * 60

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