SQLite中按特定格式日期筛选及转换为YYYY-MM-DD查询的方法

一、直接用现有格式筛选数据 #

SQLite没有原生DATE类型，你存储的Wed Aug 03 08:00:00 PDT 2022本质是字符串。可以借助datetime()函数解析该格式，结合时区转换避免误差，进行日期筛选：

筛选指定日期 #

SELECT * FROM your_table
WHERE datetime(your_date_column, 'utc') = datetime('2022-08-03', 'utc');

筛选日期范围 #

SELECT * FROM your_table
WHERE datetime(your_date_column, 'utc') BETWEEN datetime('2022-08-01', 'utc') AND datetime('2022-08-10', 'utc');

注：添加'utc'参数是为了统一时区处理，避免PDT时区导致的日期偏移问题。如果列存储的不是字符串，需要先转成文本：datetime(CAST(your_date_column AS TEXT), 'utc')

二、转换为YYYY-MM-DD格式简化查询 #

方案1：添加存储型生成列（推荐，不修改原数据） #

通过生成列自动转换并存储标准日期格式，后续直接用标准格式查询：

ALTER TABLE your_table
ADD COLUMN standard_date DATE GENERATED ALWAYS AS (date(datetime(your_date_column, 'utc'))) STORED;

之后查询就非常直观：

-- 单日期筛选
SELECT * FROM your_table WHERE standard_date = '2022-08-03';
-- 范围筛选
SELECT * FROM your_table WHERE standard_date BETWEEN '2022-08-01' AND '2022-08-10';

方案2：批量更新原列（需备份数据） #

如果允许修改原数据，可直接将所有日期转换为标准格式：

UPDATE your_table
SET your_date_column = date(datetime(your_date_column, 'utc'));

更新完成后，即可直接用YYYY-MM-DD格式编写WHERE条件。

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