文章目录

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

今天我们来详细介绍一下 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。

更多请见：http://www.mark-to-win.com/tutorial/51684.html