SQLite如何将m/d/Y H:i:s格式字符串转换为Y-m-d格式日期

SQLite日期格式转换实现方案 #

方案1：查询时临时转换（不修改原表） #

如果仅在统计查询时需要用到转换后的格式，不需要修改原表存储的数据，直接执行以下SQL即可：

SELECT 
  strftime('%Y-%m-%d', substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2)) AS date,
  place,
  app
FROM b1;

转换后的date为字符串类型，可以直接用于分组统计，示例分组查询语句如下：

SELECT 
  strftime('%Y-%m-%d', substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2)) AS date,
  place,
  app,
  COUNT(*) AS record_cnt -- 可替换为实际需要的聚合计算逻辑
FROM b1
GROUP BY date, place;

以上基础方案适用于月、日固定为两位数字的场景。如果你的数据存在单数字的月/日（比如7/5/2020这类格式），可以用更通用的截取逻辑：

SELECT 
  strftime('%Y-%m-%d', 
    substr(date, instr(date, '/', 1, 2) + 1, 4) 
    || '-' 
    || substr(date, 1, instr(date, '/') - 1) 
    || '-' 
    || substr(date, instr(date, '/') + 1, instr(date, '/', 1, 2) - instr(date, '/') - 1)
  ) AS date,
  place,
  app
FROM b1;

方案2：永久修改原表字段格式 #

如果需要把原表的date字段永久更新为目标格式，按以下步骤执行即可：

1. 新增临时字段存储转换后的日期

ALTER TABLE b1 ADD COLUMN new_date TEXT;

2. 批量更新临时字段值

UPDATE b1 
SET new_date = strftime('%Y-%m-%d', substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2));

3. 替换原字段

ALTER TABLE b1 DROP COLUMN date;
ALTER TABLE b1 RENAME COLUMN new_date TO date;

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