异常教程

SQLite 日期 & 时间（保姆级教程）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

在软件开发中，日期和时间的处理是一个高频需求场景。无论是记录用户注册时间、计算订单有效期，还是统计历史数据，都离不开对日期与时间的精准控制。SQLite 作为轻量级关系型数据库，凭借其简单易用的特性，成为许多项目中的首选存储方案。然而，许多开发者在初次接触 SQLite 的日期和时间功能时，会因为其独特的存储格式和函数设计感到困惑。本文将通过循序渐进的方式，结合实际案例，帮助读者掌握 SQLite 中日期与时间的存储、操作及高级技巧，让这一看似复杂的主题变得清晰易懂。

一、SQLite 中的日期与时间存储类型

SQLite 的设计哲学是“灵活且高效”，其对日期和时间的处理也体现了这一特点。与其他数据库不同，SQLite 不提供专门的日期时间类型，而是通过字符串、实数或整数三种格式存储日期和时间。这种设计虽然看似简单，却带来了高度的灵活性。

1.1 存储格式的三种类型

  1. 文本格式（TEXT）：以 YYYY-MM-DD HH:MM:SS 或 YYYY-MM-DD 的标准 ISO 格式存储。例如，'2023-10-25 14:30:00'。
  2. 实数格式（REAL）：以 Julian 日历 的浮点数表示，精确到秒。例如，2459823.60185185 对应 2023-10-25 14:30:00。
  3. 整数格式（INTEGER）：以 Unix 时间戳 表示，即自 1970-01-01 00:00:00 UTC 以来的秒数。例如，1698237000 对应同一时间。

比喻：这三种格式就像三种不同语言的翻译版本，内容相同但表达方式不同。SQLite 允许开发者根据需求自由选择，但推荐优先使用 文本格式，因为它可读性更强且不易出错。

1.2 实际案例：创建存储日期的表

CREATE TABLE user_activity (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    username TEXT NOT NULL,  
    login_time TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now')),  
    last_modified INTEGER DEFAULT (strftime('%s', 'now'))  
);

上述示例中，login_time 使用文本格式存储当前时间，而 last_modified 使用 Unix 时间戳。通过 strftime 函数，我们可以直接在创建表时设置默认值。

二、SQLite 日期与时间的核心函数：strftime 和 date

SQLite 提供了丰富的函数来操作日期和时间，其中最核心的当属 strftime 和 date 函数。它们如同瑞士军刀，能完成从格式转换到复杂计算的多种任务。

2.1 strftime：格式转换与计算的全能工具

strftime 函数的语法为：

strftime(format, timestring, modifier1, modifier2, ...)
  • format：目标格式字符串，例如 %Y-%m-%d。
  • timestring：输入的日期时间字符串。
  • modifiers：可选的调整参数，如 '+1 day' 或 'start of month'。

案例 1：格式转换
将 Unix 时间戳转换为可读格式：

SELECT strftime('%Y-%m-%d %H:%M', 1698237000);  
-- 返回 '2023-10-25 14:30'

案例 2：日期计算
计算某用户注册后 30 天的到期时间：

SELECT strftime('%Y-%m-%d', '2023-10-25', '+30 days');  
-- 返回 '2023-11-24'

2.2 date 函数：简化日期操作

date 函数是 strftime 的简化版，主要用于处理日期部分（不包含时间）：

date(timestring, modifier1, modifier2, ...)

案例：获取上个月的第一天

SELECT date('now', '-1 month', 'start of month');  
-- 若当前是 2023-10-25，返回 '2023-09-01'

2.3 其他实用函数

  • julianday()：将日期转换为 Julian 日历数值。
  • time()：提取时间部分（HH:MM:SS）。
  • datetime()：同时处理日期和时间，与 strftime 类似但语法更简洁。

三、常见场景与解决方案

3.1 场景 1：记录用户登录时间

-- 插入当前时间  
INSERT INTO user_activity (username, login_time)  
VALUES ('alice', strftime('%Y-%m-%d %H:%M:%S', 'now'));  

-- 查询最近一周的登录记录  
SELECT * FROM user_activity  
WHERE login_time >= strftime('%Y-%m-%d', 'now', '-7 days');

3.2 场景 2：订单有效期计算

CREATE TABLE orders (  
    order_id INTEGER PRIMARY KEY,  
    created_at TEXT DEFAULT (strftime('%Y-%m-%d')),  
    expires_at TEXT  
);  

-- 插入订单并设置有效期为 7 天后  
INSERT INTO orders (expires_at)  
VALUES (strftime('%Y-%m-%d', 'now', '+7 days'));  

-- 查询即将过期的订单（未来 3 天内）  
SELECT * FROM orders  
WHERE expires_at BETWEEN strftime('%Y-%m-%d')  
AND strftime('%Y-%m-%d', 'now', '+3 days');

3.3 场景 3：统计每月活跃用户

-- 统计 2023 年每月的活跃用户数  
SELECT  
    strftime('%Y-%m', login_time) AS month,  
    COUNT(DISTINCT username) AS active_users  
FROM user_activity  
WHERE login_time >= '2023-01-01'  
GROUP BY month;

四、陷阱与注意事项

4.1 时区问题

SQLite 默认以 UTC 时间 处理日期，但 strftime 和 date 函数的 now 关键字会根据本地时区返回结果。若需统一时区，可通过 datetime('now', 'utc') 显式指定。

4.2 空值与无效日期

插入无效日期（如 '2023-13-01'）会导致错误。建议在应用层验证数据，或使用 ON CONFLICT 处理异常。

4.3 性能优化

  • 索引优化：对频繁查询的日期列创建索引，例如： 
    CREATE INDEX idx_login_time ON user_activity(login_time);
  • 避免函数在列上使用：例如，不要写 WHERE strftime('%Y', login_time) = '2023'，而应直接比较范围： 
    WHERE login_time BETWEEN '2023-01-01' AND '2023-12-31';

五、高级技巧：自定义日期格式化

通过 strftime 的格式说明符，可以实现高度定制化的输出。例如：

-- 输出类似 '2023年10月星期三'  
SELECT strftime('%Y年%m月%W星期%d', 'now');  

-- 输出 '14:30'（24 小时制）  
SELECT strftime('%H:%M', 'now');

SQLite 的日期与时间功能虽看似复杂，但通过理解其存储逻辑、熟练运用核心函数，并结合实际案例，开发者可以高效地完成各类时间相关的任务。无论是记录用户行为、计算有效期，还是统计历史数据，SQLite 的灵活设计都能提供简洁的解决方案。建议读者通过实际编写 SQL 语句和运行查询，逐步掌握这些技巧。掌握 SQLite 的日期与时间处理，将为您的开发工作带来事半功倍的效果。

通过本文的讲解，您应该对 SQLite 中日期与时间的存储、操作及优化有了系统性认识。接下来，不妨尝试在自己的项目中实践这些技巧，进一步巩固知识并提升开发效率。

最新发布

查看更多文章