作为轻量级数据库的代表，SQLite的日期时间处理能力常被开发者低估。本文将深入解析STRFTIME、JULIANDAY等函数的高阶用法，通过真实场景演示让您彻底掌握日期计算的精髓。
一、日期函数核心武器库
1.1 STRFTIME：格式转化神器
-- 基础日期格式转换（技术栈：SQLite 3.35+）
SELECT
strftime('%Y-%m-%d', 'now') AS 标准日期, -- 2023-09-15
strftime('%H:%M:%S', 'now') AS 标准时间, -- 14:30:45
strftime('%Y年%m月%d日', '2023-12-31') AS 中文日期; -- 2023年12月31日
-- 日期维度计算（技术栈：SQLite 3.31+）
SELECT
strftime('%W', '2024-02-09') AS 周数, -- 春节所在周数
strftime('%w', '2023-12-25') AS 星期码; -- 圣诞节是星期一
此函数支持30+格式修饰符，如%j表示年中的第几天（001-366），适合制作周年庆提醒类功能。
1.2 JULIANDAY：精度计算之王
-- 精确日期差计算（技术栈：SQLite 3.38+）
SELECT
julianday('2023-09-30') - julianday('2023-09-15') AS 自然日差, -- 15.0
(julianday('2023-09-30 14:00') - julianday('2023-09-30 08:30')) * 24 AS 小时差; -- 5.5小时
-- 时段数据筛选（技术栈：SQLite 3.39+）
SELECT order_id
FROM transactions
WHERE julianday('now') - julianday(create_time) BETWEEN 0 AND 7; -- 最近7天的订单
浮点数精度可精确到秒级（1天=86400秒），特别适合金融交易时间戳计算。
二、必杀技组合应用
2.1 灵活实现DATEDIFF
-- 自定义日期差函数（技术栈：SQLite 3.37+）
CREATE TEMP TABLE projects(
id INTEGER,
start_date TEXT,
end_date TEXT
);
INSERT INTO projects VALUES
(1, '2023-01-01', '2023-01-31'),
(2, '2023-02-15', '2023-03-01');
SELECT
id,
julianday(end_date) - julianday(start_date) AS days_diff,
(julianday(end_date) - julianday(start_date)) / 30 AS month_approx
FROM projects;
结果集：
id | days_diff | month_approx
1 | 30.0 | 1.0
2 | 14.0 | 0.4666666667
2.2 复杂时段计算
-- 跨月数据统计（技术栈：SQLite 3.40+）
WITH date_series AS (
SELECT strftime('%Y-%m-%d', '2023-01-01', '+'||(value)||' days') AS dt
FROM generate_series(0, 364) -- 生成全年日期
)
SELECT
strftime('%Y-%m', dt) AS month,
COUNT(*) AS days_count,
SUM(CASE strftime('%w', dt) WHEN '0' THEN 1 ELSE 0 END) AS sundays
FROM date_series
GROUP BY month;
此方法可快速生成全年日历表，结合CASE语句完成复杂统计。
三、关联技术扩展
3.1 时区转换方案
-- 实现时区转换（技术栈：SQLite 3.36+）
SELECT
strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime') AS local_time,
strftime('%Y-%m-%d %H:%M:%S', 'now', '-8 hours') AS pst_time; -- 西八区时间
通过时间偏移参数处理时区差异，需注意跨天问题。
3.2 日期验证技巧
-- 日期有效性检测（技术栈：SQLite 3.33+）
CREATE TRIGGER validate_date
BEFORE INSERT ON events
BEGIN
SELECT CASE
WHEN strftime('%Y-%m-%d', NEW.event_date) IS NULL THEN
RAISE(ABORT, '日期格式错误')
END;
END;
利用STRFTIME的隐式转换特性实现数据校验。
四、实战场景剖析
4.1 应用场景
- 周期性报表：按周/月聚合销售数据
- 用户行为分析：计算次日留存率（精确到小时）
- 项目管理系统：自动计算任务工期
- 物联设备监控：时间序列数据清洗
4.2 技术优劣分析
优势：
- 零配置即时使用
- 内置日历算法准确处理闰年
- 毫秒级计算性能
局限：
- 缺少原生INTERVAL类型
- 时区依赖系统环境
- 大跨度计算（千年以上）存在精度丢失
五、避坑指南
- 格式陷阱：
%m格式对单数月返回两位数（01-12），需CAST(column AS INTEGER)转换 - 性能悬崖：避免在百万级数据集频繁调用日期函数
- 时区漩涡：建议统一存储UTC时间戳
- 版本差异：3.20+版本完善了夏时制处理
六、总结升华
通过STRFTIME与JULIANDAY的组合拳，我们不仅能完成基础日期处理，更可实现：
- 动态生成日期维度表
- 制作甘特图所需数据
- 精准计算服务响应时长
- 生成自然月账单周期
掌握这些技巧后，约80%的日常日期计算需求都能优雅解决。期待您能在项目中实践这些方法，如果遇到有趣的应用场景，欢迎在评论区分享交流！
评论