如何在SQLite中计算两个TEXT类型存储的日期之间的天数差

SQLite内置日期函数仅识别YYYY-MM-DD格式的日期文本，我们需要先把dd/mm/yyyy格式的字段转换为标准ISO格式，再用儒略日函数计算间隔即可批量处理，无需手动逐条操作，数千条数据可秒级完成计算。

1. 批量计算间隔天数的核心查询语句 #

将语句中的your_table替换为你的实际表名即可直接执行：

SELECT 
    arrival_date,
    departure_date,
    -- 计算结果为整数间隔天数
    CAST(
        julianday(
            -- 转换departure_date为YYYY-MM-DD格式
            substr(departure_date, 7, 4) || '-' || substr(departure_date, 4, 2) || '-' || substr(departure_date, 1, 2)
        ) 
        - 
        julianday(
            -- 转换arrival_date为YYYY-MM-DD格式
            substr(arrival_date, 7, 4) || '-' || substr(arrival_date, 4, 2) || '-' || substr(arrival_date, 1, 2)
        ) 
    AS INTEGER) AS day_interval
FROM your_table;

逻辑说明：

• substr(字段, 起始位置, 截取长度)：SQLite字符串索引从1开始，dd/mm/yyyy格式中前2位为日、第4-5位为月、第7-10位为年，拆分后拼接为标准日期格式
• julianday()：将标准格式日期转换为儒略日，两个儒略日直接相减即可得到精确间隔天数
• CAST(... AS INTEGER)：将计算结果转换为整数，避免返回小数

2. 异常场景处理 #

如果表中存在格式错误的无效日期，可在查询中添加过滤条件，避免计算报错：

SELECT 
    arrival_date,
    departure_date,
    CAST(
        julianday(substr(departure_date, 7, 4) || '-' || substr(departure_date, 4, 2) || '-' || substr(departure_date, 1, 2)) 
        - 
        julianday(substr(arrival_date, 7, 4) || '-' || substr(arrival_date, 4, 2) || '-' || substr(arrival_date, 1, 2))
    AS INTEGER) AS day_interval
FROM your_table
-- 过滤掉日期格式无效的记录
WHERE date(substr(arrival_date, 7, 4) || '-' || substr(arrival_date, 4, 2) || '-' || substr(arrival_date, 1, 2)) IS NOT NULL
AND date(substr(departure_date, 7, 4) || '-' || substr(departure_date, 4, 2) || '-' || substr(departure_date, 1, 2)) IS NOT NULL;

3. 长期优化方案 #

如果后续需要频繁使用日期计算能力，建议直接将两个字段更新为标准YYYY-MM-DD格式存储，操作前请先备份表数据：

UPDATE your_table 
SET arrival_date = substr(arrival_date, 7, 4) || '-' || substr(arrival_date, 4, 2) || '-' || substr(arrival_date, 1, 2),
    departure_date = substr(departure_date, 7, 4) || '-' || substr(departure_date, 4, 2) || '-' || substr(departure_date, 1, 2);

更新后可直接使用julianday(departure_date) - julianday(arrival_date)计算间隔，无需每次转换格式。

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