February 1, 2023
By: Kevin'

SQLite日期和时间处理

  1. SQLite 如何处理日期和时间?
  2. 字符串存储的隐患 - 错误的字符串比较
    1. 如何正确地进行字符串日期比较? - ISO 8601 格式
  3. 使用实数 (REAL) 存储日期 - Julian Day
  4. 使用整数 (INTEGER) 存储日期 - Unix Timestamp
  5. 其他有用的日期和时间函数
  6. 总结和最佳实践

SQLite 作为一款轻量级数据库, 以其简洁和高效著称. 然而, 对于许多开发者来说, 都会遇到同一个问题-- SQLite没有专门的日期和时间数据类型.

SQLite 如何处理日期和时间?

既然 SQLite 没有原生的日期和时间数据类型, 那么该如何存储和操作日期时间值呢? SQLite 提供了三种存储日期和时间值的方法:

  1. 字符串 (TEXT)
  2. 实数 (REAL) - Julian Day
  3. 整数 (INTEGER) - Unix Timestamp

在实际应用中, 应该选择哪种方式来存储日期和时间呢? 其实并没有绝对的偏好. 这三种数据类型都可以胜任日期和时间值的存储, 关键在于是否理解它们的工作原理以及潜在的陷阱.

字符串存储的隐患 - 错误的字符串比较

将日期和时间值存储为字符串的一个显而易见的好处是可读性强. 例如, '2023-10-26'这样的字符串格式, 一眼看就是2023年10月26日.

然而, 使用字符串存储日期也存在一个潜在的问题: 字符串比较可能并不符合我们期望的日期比较逻辑.

看一个例子:

SELECT '2023-10-26' > '2023-09-30';

这个布尔表达式会返回 true 还是 false呢?

以日期论, 2023-10-26 晚于 2023-09-30, 合理的结果应该是 true. 让我们在 SQLite 上试一下:

sqlite> SELECT '2023-10-26' > '2023-09-30';
1

结果果然是 1(true)!

SQLite 执行的是字符串比较, 而不是日期比较. 在字符串比较中, 它是逐字符比较的.

'2023-10-26' 和 '2023-09-30' 的前 5 个字符 '2023-' 都是相同的, 然后比较第 6 个字符, '1' 大于 '0', 所以字符串 '2023-10-26' 被判定为大于 '2023-09-30'.

在严格格式化(补0)的中文场景下没问题, 但是这样的字符串就有问题了:

sqlite> SELECT '2023-10-26' > '2023-9-30';
0

因为第6个字符是1和9进行比较, 明显不靠谱❌.

欧美习惯的日期格式是月/日/年, 这样就更加不靠谱了.

sqlite> SELECT "2/1/2022" > "1/1/2023";
1

因为只比较了月份, ❌.

如何正确地进行字符串日期比较? - ISO 8601 格式

为了让字符串比较能够像日期比较一样工作, 需要对日期字符串进行格式化, 使其符合 大端 (Big-Endian) 类似的格式. 这意味着我们需要按照 年-月-日 的顺序排列日期 component, 并且 从最大单位 (年) 开始比较.

更具体地说, 我们需要使用 ISO 8601 国际标准 日期时间格式. 这种格式将日期和时间组件按照从大到小的顺序排列:

  • 日期: YYYY-MM-DD (例如: 2023-10-26)
  • 日期时间: YYYY-MM-DD HH:MM:SS.SSS (例如: 2023-10-26 10:30:00.123)

**关键点: 为了保证正确的字符串比较, 月份和日期都应该用两位数字表示, 不足两位的前面补 0. ** 例如, 月份 9 应该写成 '09', 日期 5 应该写成 '05'.

让我们使用 ISO 8601 格式重新比较之前的日期:

sqlite> SELECT '2023-10-26' > '2023-09-30';
1
sqlite> SELECT '2023-09-30' > '2023-10-26';
0

现在, '2023-09-30' > '2023-10-26' 返回了 0 (false), 这才是我们期望的正确的日期比较结果!

示例代码: 使用 ISO 8601 格式进行字符串日期比较

-- 创建一个表, 使用 TEXT 类型存储日期 (ISO 8601 格式)
CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_name TEXT,
    event_date TEXT -- 存储 ISO 8601 格式的日期字符串
);

-- 插入一些数据
INSERT INTO events (event_name, event_date) VALUES
('会议 A', '2023-10-25'),
('会议 B', '2023-11-10'),
('会议 C', '2023-10-28');

-- 查询 event_date 在 '2023-10-27' 之后的所有事件
SELECT event_name, event_date
FROM events
WHERE event_date > '2023-10-27';

总结: 只要坚持使用 ISO 8601 格式来存储日期字符串, 就可以避免字符串比较带来的陷阱, 并获得与日期比较一致的结果.

使用实数 (REAL) 存储日期 - Julian Day

第二种存储日期和时间的方法是使用 实数类型, 在 SQLite 中, 我们可以使用 Julian Day 格式.

什么是 Julian Day? Julian Day (儒略日) 是指 自公元前 4714 年 11 月 24 日格林威治标准时间中午 12:00 以来经过的天数, 以浮点数表示.

听起来可能有点复杂, 但不需要记住具体的日期, 只需要知道 Julian Day 本质上是一个 表示日期的实数 就足够了.

如何在 SQLite 中使用 Julian Day?

SQLite 提供了 julianday() 函数来将日期时间字符串转换为 Julian Day 数值, 以及 date() 函数将 Julian Day 数值转换回日期字符串.

示例代码: 使用 Julian Day 存储和检索日期

-- 创建表, 使用 REAL 类型存储 Julian Day
CREATE TABLE t1 (
    julian_date REAL
);

-- 插入 Julian Day 值 (通过 julianday() 函数将日期字符串转换为 Julian Day)
INSERT INTO t1 (julian_date) VALUES (julianday('2023-10-26'));

-- 查询表中存储的 Julian Day 值
SELECT julian_date FROM t1;

会发现, julian_date 列的值看起来像这样: 2460242.5. 这正是日期 '2023-10-26' 对应的 Julian Day 数值.

如何将 Julian Day 转换回日期字符串? 使用 date() 函数:

SELECT
    julian_date,
    date(julian_date) AS date_string -- 使用 date() 函数将 Julian Day 转换为日期字符串
FROM t1;

执行上述查询, 将看到 date_string 列显示了日期 '2023-10-26'.

总结: Julian Day 提供了一种以数值形式存储日期的方式, 方便进行日期计算和比较. SQLite 提供了 julianday() 和 date() 函数来方便地进行 Julian Day 和日期字符串之间的转换.

使用整数 (INTEGER) 存储日期 - Unix Timestamp

最后一种方法是使用 整数类型 来存储日期和时间, 更具体地说, 可以使用 Unix Timestamp (Unix 时间戳).

什么是 Unix Timestamp? Unix Timestamp 是指 自 1970 年 1 月 1 日 00:00:00 UTC (协调世界时) 以来经过的秒数, 不考虑闰秒. 它也是一种广泛使用的日期和时间表示方法, 特别是在计算机系统中.

如何在 SQLite 中使用 Unix Timestamp?

SQLite 提供了 unixepoch() 函数来将日期时间字符串转换为 Unix Timestamp 数值, 以及 date() 函数(配合 unixepoch 字符串参数)将 Unix Timestamp 数值转换回日期字符串.

示例代码: 使用 Unix Timestamp 存储和检索日期

-- 创建表, 使用 INTEGER 类型存储 Unix Timestamp
CREATE TABLE t2 (
    unix_date INTEGER
);

-- 插入 Unix Timestamp 值 (通过 unixepoch() 函数将日期字符串转换为 Unix Timestamp)
INSERT INTO t2 (unix_date) VALUES (unixepoch('2023-10-26'));

-- 查询表中存储的 Unix Timestamp 值
SELECT unix_date, typeof(unix_date) FROM t2;

会看到 unix_date 列的值是一个很大的整数, 例如 1698268800. 这就是日期 '2023-10-26' 对应的 Unix Timestamp 数值 (秒数). typeof(unix_date) 函数确认了 unix_date 列的数据类型是 integer.

如何将 Unix Timestamp 转换回日期字符串? 使用 date() 函数, 并指定第二个参数为字符串 'unixepoch':

SELECT
    unix_date,
    date(unix_date, 'unixepoch') AS date_string -- 使用 date() 函数和 'unixepoch' 参数将 Unix Timestamp 转换为日期字符串
FROM t2;

执行上述查询, date_string 列将显示日期 '2023-10-26'.

总结: Unix Timestamp 是一种通用的时间表示方法, 在不同系统之间具有良好的兼容性. SQLite 提供了 unixepoch() 和 date(..., 'unixepoch') 函数来支持 Unix Timestamp 的转换和操作.

其他有用的日期和时间函数

除了我们已经使用过的 date(), julianday(), 和 unixepoch() 函数之外, SQLite 还提供了其他一些有用的日期和时间函数, 包括:

  • time(日期时间字符串): 返回时间, 格式为 HH:MM:SS.
  • datetime(日期时间字符串): 返回日期和时间, 格式为 YYYY-MM-DD HH:MM:SS.
  • strftime(格式字符串, 日期时间字符串): 功能强大的日期和时间格式化函数, 可以根据指定的格式字符串将日期和时间格式化成各种需要的形式.

示例: 使用其他日期时间函数

SELECT
    time('now') AS current_time, -- 获取当前时间
    datetime('now') AS current_datetime, -- 获取当前日期和时间
    strftime('%Y年%m月%d日 %H时%M分%S秒', 'now') AS formatted_datetime; -- 格式化日期时间

总结和最佳实践

SQLite 虽然没有原生的日期和时间数据类型, 但它提供了灵活的方法来处理日期和时间值. 可以根据实际需求选择合适的存储方式:

  • 字符串 (TEXT, ISO 8601 格式): 人类可读性好, 易于调试, 但需要注意字符串比较的陷阱. 适用于对日期进行简单比较和展示的场景.
  • 实数 (REAL, Julian Day): 适合需要进行日期计算和精确日期比较的场景.
  • 整数 (INTEGER, Unix Timestamp): 通用性强, 跨系统兼容性好, 适合与其他系统进行日期时间数据交换的场景.

最佳实践:

  • 使用 ISO 8601 格式的字符串 (TEXT) 来存储日期和时间, 因为它兼顾了可读性和正确的比较特性. 尤其是在不需要进行复杂日期计算的场景下, 字符串是更简单直接的选择.
  • 如果需要进行复杂的日期计算或与其他系统进行数据交换, 可以考虑使用 Julian Day 或 Unix Timestamp.
  • 熟悉 SQLite 提供的日期和时间函数, 例如 date(), julianday(), unixepoch(), strftime() 等, 它们能够更有效地处理日期和时间数据.
Tags: sqlite sql