SQLite库笔记：日期和时间函数

1. 函数概述

SQLite支持7个日期和时间函数，如下：

1	date(time-value, modifier, modifier, ...)	返回YYYY-MM-DD格式的日期
2	time(time-value, modifier, modifier, ...)	返回HH:MM:SS格式的时间
3	datetime(time-value, modifier, modifier, ...)	返回YYYY-MM-DD HH:MM:SS格式的日期时间
4	julianday(time-value, modifier, modifier, ...)	返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数
5	unixepoch(time-value, modifier, modifier, ...)	返回unix时间戳，即1970-01-01 00:00:00 UTC以来的秒数
6	strftime(format, time-value, modifier, modifier, ...)	返回format指定格式的日期时间
7	timediff(time-value A, time-value B)	返回一个字符串，该字符串描述为到达时间A而必须添加到B的时间量

其中strftime()的format格式字符串支持在标准 C 库的 strftime()函数中找到的最常见替换项，以及两个新的替换项，即 %f 和 %J。 以下是有效 strftime()替换的完整列表，截至SQLite版本 3.46.0 （2024-05-23）。SQLite 的早期版本可能不支持所有替换。如果未定义或不受支持看到替换，结果为 NULL。

%d	月份的第几天: 01-31
%e	月份的第几天，不带前导零: 1-31
%f	带小数部分的秒: SS.SSS
%F	ISO 8601日期: YYYY-MM-DD
%G	ISO 8601年份对应%V
%g	2位ISO 8601年份对应%V
%H	小时: 00-24
%I	12小时制的小时: 01-12
%j	年份的第几天: 001-366
%J	儒略历日（小数）
%k	不带前导零的小时: 0-24
%l	12小时制的小时，不带前导零: 1-12
%m	月份: 01-12
%M	分钟: 00-59
%p	取决于小时的"AM" 或"PM"
%P	取决于小时的"am" or "pm"
%R	ISO 8601时间: HH:MM
%s	1970-01-01以来的秒数
%S	秒: 00-59
%T	ISO 8601时间: HH:MM:SS
%U	年份的第几周（00-53）-第01周从第一个星期日开始
%u	一周的第几天（1-7），其中周一为第1天
%V	ISO 8601一年中第几个星期
%w	一周的第几天（0-6），其中周日为0
%W	一年的第几周 (00-53) – 第01周从第一个星期日开始
%Y	年份: 0000-9999
%%	%

strftime()比较灵活，其它日期和时间函数可以用strftime()表示：

函数	等同于strftime()
date(...)	strftime('%Y-%m-%d', ...)
time(...)	strftime('%H:%M:%S', ...)
datetime(...)	strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...)	strftime('%J', ...)

2. time-value时间字符串

上面列表的time-value时间字符串通常是一个字符串，可以使用下列格式：

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

上述格式中标红的“T”是分隔的文字字符。倒数第二个格式字符串“now”，可转换为当前日期。

3. modifier修饰符

对于除 timediff()以外的所有日期/时间函数， time-value 参数后面可以跟零个或多个modifier修饰符，这些修饰符更改日期时间。每个修饰符都是应用于其左侧时间值的转换。注意，顺序很重要。可用的修饰符如下

•     NNN days
•     NNN hours
•     NNN minutes
•     NNN seconds
•     NNN months
•     NNN years
•     ±HH:MM
•     ±HH:MM:SS
•     ±HH:MM:SS.SSS
•     ±YYYY-MM-DD
•     ±YYYY-MM-DD HH:MM
•     ±YYYY-MM-DD HH:MM:SS
•     ±YYYY-MM-DD HH:MM:SS.SSS
•     ceiling
•     floor
•     start of month
•     start of year
•     start of day
•     weekday N
•     unixepoch
•     julianday
•     auto
•     localtime
•     utc
•     subsec
•     subsecond

4. 示例

计算当前日期：

sqlite> select date(); 
2024-08-09

计算unix时间戳的日期和时间、某日期时间的unix时间戳：

sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
sqlite> select unixepoch('2004-08-19 18:51:06');
1092941466

计算2024-01-01 12:00:00距离1970-01-01有多久：

sqlite> select timediff('2024-01-01 12:00:00', '1970-01-01');
+0054-00-00 12:00:00.000

计算当前日期时间（UTC）、本地时区的日期时间：

sqlite> select strftime('%F %T');
2024-08-09 13:57:04
sqlite> select strftime('%Y-%m-%d %H:%M:%S');
2024-08-09 13:57:04
sqlite> select strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime');
2024-08-09 21:57:04

 

注意：SQLite默认是UTC时间，获取当地时间可以加上'localtime'修饰符。