SQLite - 日期与时间

SQLite - 日期和时间

SQLite 没有专门的 DATE 或 TIME 数据类型。相反，它将日期和时间存储为 TEXT（ISO 8601 字符串）、REAL（儒略日数）或 INTEGER（Unix 时间戳）。一组功能强大的内置函数让您可以轻松操作这些值。

序号	函数	示例输出
1	date(timestring, modifiers…)	以 ‘YYYY-MM-DD’ 格式返回日期
2	time(timestring, modifiers…)	以 ‘HH:MM:SS’ 格式返回时间
3	datetime(timestring, modifiers…)	返回 ‘YYYY-MM-DD HH:MM:SS’
4	julianday(timestring, modifiers…)	返回儒略日数
5	strftime(format, timestring, modifiers…)	返回自定义格式的日期/时间字符串

timestring 参数可以是识别格式的日期/时间值，或者 ‘now’ 表示当前时间。修饰符会调整 timestring 值。

最佳实践：存储日期

对于大多数应用程序，建议将日期和时间以 TEXT 类型存储为 ISO 8601 格式（YYYY-MM-DD HH:MM:SS）。这种格式可读性高，无歧义，并且作为文本排序正确。将所有时间存储为 UTC 以避免时区问题，并在应用程序层转换为本地时间。

常用修饰符

修饰符从左到右应用，以改变时间值。

• N days（或 hours、minutes、seconds、months、years）：添加或减去时间单位。示例：'+7 days'、'-1 month'。
• start of month（或 year、day）：将日期回溯到该时间段的开始。
• weekday N：将日期推进到下一个星期 N（0=星期日，1=星期一，…）。
• localtime：假定时间字符串是 UTC，并将其转换为本地时区。
• utc：假定时间字符串是本地时区，并将其转换为 UTC。

strftime() 格式说明符

strftime() 函数对于自定义格式化非常有用。以下是一些常见的格式说明符：

替换符	描述
%Y	年份：0000-9999
%m	月份：01-12
%d	月份中的日期：01-31
%H	小时：00-23
%M	分钟：00-59
%S	秒：00-59
%s	自 1970-01-01 以来的秒数 (Unix 时间戳)
%w	星期几：0-6 (星期日是 0)
%j	年份中的日期：001-366
%W	年份中的周数：00-53

实用示例

假设我们有一个 events 表：CREATE TABLE events (name TEXT, event_date TEXT);

-- 获取当前日期和时间 (UTC)
SELECT datetime('now');
-- 结果: '2024-05-10 14:30:00' (示例)

-- 获取下个月第一天的日期
SELECT date('now', 'start of month', '+1 month');
-- 结果: '2024-06-01'

-- 获取当前的 Unix 时间戳
SELECT strftime('%s', 'now');
-- 结果: '1715351400' (示例)

-- 以友好格式（例如 'Friday, May 10, 2024'）格式化今天的日期
SELECT strftime('%A, %B %d, %Y', 'now');

-- 查找未来 7 天内发生的所有事件
SELECT * FROM events
WHERE event_date BETWEEN date('now') AND date('now', '+7 days');

-- 计算两个日期之间的天数
SELECT julianday('2025-01-01') - julianday('now');

-- 将 Unix 时间戳转换为人类可读的日期时间
SELECT datetime(1672531200, 'unixepoch');
-- 结果: '2023-01-01 00:00:00'

-- 将 Unix 时间戳转换为本地时间
SELECT datetime(1672531200, 'unixepoch', 'localtime');