SQLite 中的日期和时间

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

日期和时间类型

首先，SQLite 使用一种动态的数据类型系统，数据的类型只和数据本身有关，与字段定义的类型无关。这一点和其他数据库不同，通常数据库字段的类型决定了可以存储的数据类型。

其次，SQLite 没有提供专门用于存储日期和时间的数据类型。通常我们可以使用 TEXT、REAL 或者 INTEGER 存储日期和时间，SQLite 提供了支持这些数据类型的日期和时间函数。

• TEXT 存储日期和时间时使用 ISO8601 格式的字符串（“YYYY-MM-DD HH:MM:SS.SSS”）。
• REAL 存储日期时表示儒略日（Julian Day），也就是格里历（阳历）公元前 4714 年 11 月 24 日 UTC 正午到该时间的天数。
• INTEGER 存储日期和时间时表示 Unix 时间戳，也就是 1970-01-01 00:00:00 UTC 到该时间的秒数。

应用程序可以选择以上三种格式之一存储日期和时间，SQLite 内置函数可以支持它们之间的格式转换。

日期和时间函数

SQLite 支持以下五个日期和时间函数：

序号	函数	实例
1	date(timestring, modifier, modifier, ...)	以 YYYY-MM-DD 格式返回日期。
2	time(timestring, modifier, modifier, ...)	以 HH:MM:SS 格式返回时间。
3	datetime(timestring, modifier, modifier, ...)	以 YYYY-MM-DD HH:MM:SS 格式返回。
4	julianday(timestring, modifier, modifier, ...)	这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。
5	strftime(format, timestring, modifier, modifier, ...)	这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

上述五个日期和时间函数把时间值(timestring)作为参数。时间值(timestring)后跟零个或多个 modifier 修饰符。

date() 函数返回的日期格式为 YYYY-MM-DD，例如：

sqlite> select date();
2021-08-06

time() 函数返回的时间格式为 HH:MM:SS，例如：

sqlite> select time();
03:57:53

datetime() 函数返回的日期时间格式为 YYYY-MM-DD HH:MM:SS，例如：

sqlite> select datetime();
2021-08-06 03:58:39

时间值（timestring）

时间值使用以下格式之一。可以使用 "T" 作为分隔日期和时间的文字字符。

序号	时间字符串	实例
1	YYYY-MM-DD	2010-12-30
2	YYYY-MM-DD HH:MM	2010-12-30 12:10
3	YYYY-MM-DD HH:MM:SS.SSS	2010-12-30 12:10:04.100
4	MM-DD-YYYY HH:MM	12-30-2010 12:10
5	HH:MM	12:10
6	YYYY-MM-DDTHH:MM	2010-12-30 12:10
7	HH:MM:SS	12:10:01
8	YYYYMMDD HHMMSS	20101230 121001
9	now	2013-05-07

修饰符（Modifier）

时间值后边可跟着零个或多个的修饰符，修饰符通常用来，增加减去指定的时间。NNN表示增加减少的值，后面是单位。

• NNN days

• NNN hours

• NNN minutes

• NNN.NNNN seconds

• NNN months

• NNN years

• start of month

• start of year

• start of day

• weekday N

• unixepoch

• localtime

• utc

 上面第 1 到第 6 个修饰符用于增加指定的时间。例如：

sqlite> select date('2021-01-01', '+5 days');
2021-01-06

第 7 到第 9 个修饰符用于将日期转换为当前月、年或者日的开始，例如：

-- 返回当前月份的最后一天
sqlite> select date('now','start of month','+1 month','-1 day');
2021-08-31

第 10 个修饰符（weekday N）用于将日期转换为（如有必要）下一周中的指定日期。星期天为 0，星期一为 1，依次类推。如果被转换的日期已经是当前周中的指定日期，不做任何修改。例如

-- 返回当前年份中十月第一个星期二Compute the date of the first Tuesday in October for the current year.
sqlite> select date('now','start of year','+9 months','weekday 2');
2021-10-05

第 11 个修饰符（unixepoch）只能修改 DDDDDDDDDD 格式的时间值。该修饰符将 DDDDDDDDDD 转换为 Unix 时间戳（1970 年以来的秒数），而不是通常情况下的儒略日天数。例如：

-- 返回 Unix 时间戳 1092941466 对应的日期和时间
sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

如果 unixepoch 修饰符前面不是 DDDDDDDDDD 格式的时间值，或者它们之间存在其他修饰符，转换的结果不可预期。

第 12 个修饰符（localtime）将左侧的 UTC 时间值转换为本地时区对应的时间值。如果左侧的时间值不是 UTC 时区，转换的结果不可预期。例如：

-- 计算 Unix 时间戳 1092941466 对应的时间，并且转换为本地时间
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-20 02:51:06

第 13 个修饰符（utc）执行的转换操作和 localtime 正好相反，将左侧的本地时间转化为 UCT 时间。如果左侧的时间值不是本地时间，转换的结果不可预期。

格式化（format）

SQLite 提供了非常方便的函数 strftime() 来格式化任何日期和时间。

替换	描述
%d	一月中的第几天，01-31
%f	带小数部分的秒，SS.SSS
%H	小时，00-23
%j	一年中的第几天，001-366
%J	儒略日数，DDDD.DDDD
%m	月，01-12
%M	分，00-59
%s	从 1970-01-01 算起的秒数
%S	秒，00-59
%w	一周中的第几天，0-6 (0 is Sunday)
%W	一年中的第几周，01-53
%Y	年，YYYY
%%	% symbol

实际上其他 4 个函数都可以使用 strftime() 函数表示：

• date(…) 等价于 strftime('%Y-%m-%d', …)
• time(…) 等价于 strftime('%H:%M:%S', …)
• datetime(…) 等价于 strftime('%Y-%m-%d %H:%M:%S', …)
• julianday(…) 等价于 strftime('%J', …)

例如：

-- 返回当前日期
SELECT date('now');

sqlite> select strftime('%Y-%m-%d %H:%M:%S');
2021-08-06 06:35:00



select strftime('%Y-%m-%d %H:%M:%S','now');
select strftime('%Y-%m-%d %H:%M:%S','2010-12-30');
select strftime('%Y-%m-%d %H:%M:%S','2010-12-30 12:10:04.100');

当使用date()、date('now')、time()、time('now')、datetime() 、datetime('now')获取当前时间时
默认是utc时间，需要转换为本地时间

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

字符串“now”表示当前日期和时间，时区为 UTC。

小数秒 SS.SSS 可以包含一个或多个小数位数。

参考：

SQLite 日期 & 时间 | 菜鸟教程