SQLite 中的日期和时间

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目录

        日期和时间类型
        日期和时间函数
            指定时间值
            指定修饰符

今天我们来详细介绍一下 SQLite 中的日期和时间数据类型，以及相关的处理函数。

日期和时间类型

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

其次，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 提供了以下 5 种日期和时间函数：

    date(time-value, modifier, modifier, …)
    time(time-value, modifier, modifier, …)
    datetime(time-value, modifier, modifier, …)
    julianday(time-value, modifier, modifier, …)
    strftime(format, time-value, modifier, modifier, …)

以上函数都可以接收一个时间值作为参数，后面可以增加任意多个修饰符。strftime() 函数的第一个参数用于指定格式化字符串。

日期和时间函数使用 IS0-8601 日期和时间格式的一个子集。

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


julianday() 函数返回儒略日的天数，包括时间信息。例如：

sqlite> select julianday(), datetime();
2459432.66719566|2021-08-06 04:00:45


strftime() 函数返回日期和时间的格式由第一个参数中的格式化字符串决定。格式化字符串支持标准 C 代码库中最常用的控制符以及 %f 和 %J。以下是一个完整的 strftime() 控制符列表：

    %d 一月中的第几天：00-31
    %f 包含毫秒的秒数：SS.SSS
    %H 小时：00-24
    %j 一年中的第几天：001-366
    %J 儒略日的天数
    %m 月份：01-12
    %M 分钟：00-59
    %s 1970-01-01 以来的秒数
    %S 秒数：00-59
    %w 一周中的第几天：0-6，星期天为 0
    %W 一年中的第几周：00-53
    %Y 年份：0000-9999
    %% 字符 %

例如：

sqlite> select strftime('The date and time: %Y-%m-%d %H:%M:%S');
The date and time: 2021-08-06 06:34:09

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

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

例如：

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


提供这些函数主要是为了方便使用，提高效率。
指定时间值

时间值参数可以使用以下格式之一。时间值通常是一个字符串，只有在第 12 种格式时可以指定一个整数或者浮点数。

    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

例如：

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

-- 返回当前 Unix 时间戳
SELECT strftime('%s','now');

-- 计算 2000 年以来的天数
SELECT julianday('now') - julianday('2000-01-01');

-- 计算 2000 年以来的秒数
SELECT strftime('%s','now') - strftime('%s','2000-01-01 00:00:00');

-- 计算 Unix 纪元以来的秒数，类似于 strftime('%s','now')，但是包含小数秒

SELECT (julianday('now') - 2440587.5)*86400.0;


第 5 种到第 7 种格式中的字符 T 用于分隔日期和时间，遵循 ISO-8601 标准。

第 8 种到第 10 种格式只包含时间信息，默认日期为 2000-01-01。

第 11 种格式中的字符串“now”表示当前日期和时间，时区为 UTC。

第 12 种格式是儒略日的天数，支持整型和浮点型数字值。

第 2 种到第 10 种格式可以增加一个时区标识符“[±]HH:MM”或者“Z”。日期和时间函数在内部使用 UTC 或者“zulu”时间，因此后缀“Z”没有实际作用。任何非零的“HH:MM”后缀都会从指定日期和时间中减去相应的时间，从而转化为 UTC 时间。例如，以下所有的时间值都等价：

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685

第 4、第 7 以及第 10 种格式中，小数秒 SS.SSS 可以包含一个或多个小数位数。示例中只显示了 3 位小数，因为只有前三位小数是有效数字，不过输入字符串中可以包含更少或更多小数，日期/时间函数可以正确处理这些情况。同样，第 12 中格式显示了 10 位数字，但是日期/时间函数可以接收能够表示为儒略日天数的任意位数的数字。
指定修饰符

时间值参数的后面可以增加任意多个修饰符，用于改变日期和时间的数值。每个修饰符都会对其左侧的时间进行转换，修饰符从左至右依次应用。以下是可用的修饰符：

    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 个修饰符用于增加指定的时间。修饰符名称最后的字符“s”可以省略。例如：

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

    
注意，“±NNN months”首先会将日期转化为 YYYY-MM-DD 格式，然后增加 ±NNN 月份，最后规范化结果。例如，日期 2001-03-31 使用修饰符 ‘+1 month’ 时首先产生 2001-04-31，由于 4 月份只有 30 天，因此最终规范化的结果为 2001-05-01。类似，如果原始数据是闰年中的 2 月 29 日，修饰符是 ±N years 并且 N 不是 4 的倍数，也会产生同样的效果。

第 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 时间。如果左侧的时间值不是本地时间，转换的结果不可预期。