5月31日 23:58

SQLite 日期时间函数怎么用？时间存储和查询怎么避坑？

SQLite 没有专门的日期时间类型，这是理解它日期函数的第一步。你可以把时间存成 TEXT、INTEGER 或 REAL，然后用 date()、time()、datetime()、julianday()、strftime()、unixepoch() 这些函数处理。

最常见、也最容易维护的做法，是把业务时间统一存成 UTC Unix 时间戳，或者存成 ISO 8601 格式的 TEXT。关键不是哪一种绝对最好，而是全项目统一。混用本地时间、UTC、字符串和秒级时间戳，后面查 bug 会非常痛苦，尤其跨设备同步时很难判断到底是谁转换错了。

SQLite 支持哪些时间表示？

TEXT 通常写成 YYYY-MM-DD HH:MM:SS，可读性好，按字典序排序也能和时间顺序一致。INTEGER 通常存 Unix 时间戳，计算和比较方便，适合移动端同步、过期判断。REAL 存 Julian day，日常业务较少直接使用。

sql
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  start_at INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_events_start_at ON events(start_at);

如果你经常和服务端交换数据，INTEGER 秒级或毫秒级时间戳更稳定；如果运营、排查、手工导出很多，TEXT 可读性更好。无论选哪种，都要写进团队约定。

常用日期函数怎么用？

date() 返回日期，time() 返回时间，datetime() 返回日期时间，strftime() 负责格式化。now 表示当前时间，SQLite 内部按 UTC 处理，再根据修饰符转换。

sql
SELECT date('now');
SELECT time('now');
SELECT datetime('now');
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');
SELECT unixepoch('now');

日期计算通过修饰符完成：

sql
SELECT date('now', '+7 days');
SELECT datetime('now', '+3 hours', '-30 minutes');
SELECT julianday('now') - julianday('2026-01-01');
SELECT datetime(1739869440, 'unixepoch');

julianday() 适合算两个时间之间差多少天，unixepoch() 更适合和后端接口、移动端本地存储配合。

查询日期范围怎么写才走索引？

最常见的性能坑，是在 WHERE 里给列套函数。比如下面这种写法直观，但可能让索引失效：

sql
-- 不推荐：对列调用 date()
SELECT * FROM orders
WHERE date(order_date) = date('now');

更好的做法是把范围边界算好，再直接比较列：

sql
-- TEXT 日期
SELECT * FROM orders
WHERE order_date >= datetime('now', 'start of day')
  AND order_date <  datetime('now', 'start of day', '+1 day');

-- INTEGER 时间戳
SELECT * FROM orders
WHERE paid_at >= unixepoch('now', 'start of day')
  AND paid_at <  unixepoch('now', 'start of day', '+1 day');

这样数据库更容易使用 order_date 或 paid_at 上的索引。数据量小的时候差距不明显，等订单表涨到几十万行，这个写法差距会很明显。

时区应该怎么处理？

SQLite 的 datetime('now') 返回 UTC 时间。localtime 会转成本地时区，但移动端、服务器、用户设备的本地时区可能不同。业务存储层建议统一 UTC，展示层再转本地时间。

sql
SELECT datetime('now');              -- UTC
SELECT datetime('now', 'localtime'); -- 本地时间
SELECT datetime('now', '+8 hours');  -- 固定偏移，不等于完整时区规则

不要把 +8 hours 当成时区系统。它只是固定偏移，处理不了夏令时，也不知道历史时区变更。真正复杂的时区展示，交给应用层的时间库更稳。

生成列能不能帮忙优化？

如果你存的是完整时间，但经常按日期查，可以考虑生成列或额外冗余一个日期字段。这样既保留完整时间，又能让日期查询走索引。

sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  paid_at TEXT NOT NULL,
  paid_date TEXT GENERATED ALWAYS AS (date(paid_at)) STORED
);

CREATE INDEX idx_orders_paid_date ON orders(paid_date);

取舍点是写入复杂度和存储空间。高频查询、低频写入的表很适合；高频写入的日志表就要评估额外索引带来的成本。

追问

SQLite 日期应该存 TEXT 还是 INTEGER？

TEXT 可读性好，排查和导出方便，格式统一时也能排序。INTEGER 时间戳比较和计算更直接，适合移动端同步、过期判断、跨语言传输。取舍看谁是主要消费者：人经常看就偏 TEXT，程序高频算就偏 INTEGER。边界是必须统一时区和精度，秒和毫秒混用会制造很隐蔽的 bug。

为什么不建议在 WHERE 里写 date(column)？

因为对列调用函数后，数据库往往不能直接使用普通索引，只能先算每一行的日期再比较。数据少时看不出来，数据多时会变成全表扫描。更好的做法是把目标日期转换成起止范围，再让列和常量比较。这个坑在“查今天订单”“查本月记录”里非常常见。

UTC、本地时间和 +8 hours 有什么区别？

UTC 是统一存储基准，本地时间是根据运行环境的时区规则转换出来的结果。+8 hours 只是固定加 8 小时，不理解地区、夏令时和历史规则。业务存储建议用 UTC，展示时用应用层按用户时区格式化。踩坑点是服务端和客户端都以为自己存的是本地时间，最后同一条记录相差 8 小时。

如何计算两个时间相差多少天？

如果是 TEXT 日期，可以用 julianday(end) - julianday(start)。如果是 INTEGER 时间戳，可以直接相减再除以 86400。取舍在于精度和可读性：日期报表用 julianday 方便，秒级倒计时用时间戳更清楚。边界是跨夏令时的“自然日”和“24 小时”不是同一件事，业务要先定义清楚。

生成列适合所有日期查询吗？

不适合。生成列适合某个派生值被频繁查询，比如 paid_date、月份、年份。它会增加写入成本和存储空间，低频查询没必要为了它复杂化表结构。另一个边界是 SQLite 版本：生成列需要较新的 SQLite，移动端要确认系统内置版本或随包 SQLite 是否支持。上线前别只在开发机 SQLite 版本上测试。

标签：Sqlite