SQLite 中的日期和時(shí)間

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目錄

        日期和時(shí)間類型
        日期和時(shí)間函數(shù)
            指定時(shí)間值
            指定修飾符

今天我們來詳細(xì)介紹一下 SQLite 中的日期和時(shí)間數(shù)據(jù)類型，以及相關(guān)的處理函數(shù)。

日期和時(shí)間類型

首先，SQLite 使用一種動(dòng)態(tài)的數(shù)據(jù)類型系統(tǒng)，數(shù)據(jù)的類型只和數(shù)據(jù)本身有關(guān)，與字段定義的類型無關(guān)。例如，我們可以定義一個(gè) INTEGER 類型的字段，然后用于存儲(chǔ)字符串。這一點(diǎn)和其他數(shù)據(jù)庫不同，通常數(shù)據(jù)庫字段的類型決定了可以存儲(chǔ)的數(shù)據(jù)類型。

其次，SQLite 沒有提供專門用于存儲(chǔ)日期和時(shí)間的數(shù)據(jù)類型。通常我們可以使用 TEXT、REAL 或者 INTEGER 存儲(chǔ)日期和時(shí)間，SQLite 提供了支持這些數(shù)據(jù)類型的日期和時(shí)間函數(shù)。

    TEXT 存儲(chǔ)日期和時(shí)間時(shí)使用 ISO8601 格式的字符串（“YYYY-MM-DD HH:MM:SS.SSS”）。
    REAL 存儲(chǔ)日期時(shí)表示儒略日（Julian Day），也就是格里歷（陽歷）公元前 4714 年 11 月 24 日 UTC 正午到該時(shí)間的天數(shù)。
    INTEGER 存儲(chǔ)日期和時(shí)間時(shí)表示 Unix 時(shí)間戳，也就是 1970-01-01 00:00:00 UTC 到該時(shí)間的秒數(shù)。

應(yīng)用程序可以選擇以上三種格式之一存儲(chǔ)日期和時(shí)間，SQLite 內(nèi)置函數(shù)可以支持它們之間的格式轉(zhuǎn)換。
日期和時(shí)間函數(shù)

SQLite 提供了以下 5 種日期和時(shí)間函數(shù)：

    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, …)

以上函數(shù)都可以接收一個(gè)時(shí)間值作為參數(shù)，后面可以增加任意多個(gè)修飾符。strftime() 函數(shù)的第一個(gè)參數(shù)用于指定格式化字符串。

日期和時(shí)間函數(shù)使用 IS0-8601 日期和時(shí)間格式的一個(gè)子集。

date() 函數(shù)返回的日期格式為 YYYY-MM-DD，例如：

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


time() 函數(shù)返回的時(shí)間格式為 HH:MM:SS，例如：

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

datetime() 函數(shù)返回的日期時(shí)間格式為 YYYY-MM-DD HH:MM:SS，例如：

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


julianday() 函數(shù)返回儒略日的天數(shù)，包括時(shí)間信息。例如：

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


strftime() 函數(shù)返回日期和時(shí)間的格式由第一個(gè)參數(shù)中的格式化字符串決定。格式化字符串支持標(biāo)準(zhǔn) C 代碼庫中最常用的控制符以及 %f 和 %J。以下是一個(gè)完整的 strftime() 控制符列表：

    %d 一月中的第幾天：00-31
    %f 包含毫秒的秒數(shù)：SS.SSS
    %H 小時(shí)：00-24
    %j 一年中的第幾天：001-366
    %J 儒略日的天數(shù)
    %m 月份：01-12
    %M 分鐘：00-59
    %s 1970-01-01 以來的秒數(shù)
    %S 秒數(shù)：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

實(shí)際上其他 4 個(gè)函數(shù)都可以使用 strftime() 函數(shù)表示：

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

例如：

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


提供這些函數(shù)主要是為了方便使用，提高效率。
指定時(shí)間值

時(shí)間值參數(shù)可以使用以下格式之一。時(shí)間值通常是一個(gè)字符串，只有在第 12 種格式時(shí)可以指定一個(gè)整數(shù)或者浮點(diǎn)數(shù)。

    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

例如：

-- 返回當(dāng)前日期
SELECT date('now');

-- 返回當(dāng)前 Unix 時(shí)間戳
SELECT strftime('%s','now');

-- 計(jì)算 2000 年以來的天數(shù)
SELECT julianday('now') - julianday('2000-01-01');

-- 計(jì)算 2000 年以來的秒數(shù)
SELECT strftime('%s','now') - strftime('%s','2000-01-01 00:00:00');

-- 計(jì)算 Unix 紀(jì)元以來的秒數(shù)，類似于 strftime('%s','now')，但是包含小數(shù)秒

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


第 5 種到第 7 種格式中的字符 T 用于分隔日期和時(shí)間，遵循 ISO-8601 標(biāo)準(zhǔn)。

第 8 種到第 10 種格式只包含時(shí)間信息，默認(rèn)日期為 2000-01-01。

第 11 種格式中的字符串“now”表示當(dāng)前日期和時(shí)間，時(shí)區(qū)為 UTC。

第 12 種格式是儒略日的天數(shù)，支持整型和浮點(diǎn)型數(shù)字值。

第 2 種到第 10 種格式可以增加一個(gè)時(shí)區(qū)標(biāo)識(shí)符“[±]HH:MM”或者“Z”。日期和時(shí)間函數(shù)在內(nèi)部使用 UTC 或者“zulu”時(shí)間，因此后綴“Z”沒有實(shí)際作用。任何非零的“HH:MM”后綴都會(huì)從指定日期和時(shí)間中減去相應(yīng)的時(shí)間，從而轉(zhuǎn)化為 UTC 時(shí)間。例如，以下所有的時(shí)間值都等價(jià)：

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 種格式中，小數(shù)秒 SS.SSS 可以包含一個(gè)或多個(gè)小數(shù)位數(shù)。示例中只顯示了 3 位小數(shù)，因?yàn)橹挥星叭恍?shù)是有效數(shù)字，不過輸入字符串中可以包含更少或更多小數(shù)，日期/時(shí)間函數(shù)可以正確處理這些情況。同樣，第 12 中格式顯示了 10 位數(shù)字，但是日期/時(shí)間函數(shù)可以接收能夠表示為儒略日天數(shù)的任意位數(shù)的數(shù)字。
指定修飾符

時(shí)間值參數(shù)的后面可以增加任意多個(gè)修飾符，用于改變?nèi)掌诤蜁r(shí)間的數(shù)值。每個(gè)修飾符都會(huì)對其左側(cè)的時(shí)間進(jìn)行轉(zhuǎn)換，修飾符從左至右依次應(yīng)用。以下是可用的修飾符：

    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 個(gè)修飾符用于增加指定的時(shí)間。修飾符名稱最后的字符“s”可以省略。例如：

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

    
注意，“±NNN months”首先會(huì)將日期轉(zhuǎn)化為 YYYY-MM-DD 格式，然后增加 ±NNN 月份，最后規(guī)范化結(jié)果。例如，日期 2001-03-31 使用修飾符 ‘+1 month’ 時(shí)首先產(chǎn)生 2001-04-31，由于 4 月份只有 30 天，因此最終規(guī)范化的結(jié)果為 2001-05-01。類似，如果原始數(shù)據(jù)是閏年中的 2 月 29 日，修飾符是 ±N years 并且 N 不是 4 的倍數(shù)，也會(huì)產(chǎn)生同樣的效果。

第 7 到第 9 個(gè)修飾符用于將日期轉(zhuǎn)換為當(dāng)前月、年或者日的開始，例如：

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

第 10 個(gè)修飾符（weekday N）用于將日期轉(zhuǎn)換為（如有必要）下一周中的指定日期。星期天為 0，星期一為 1，依次類推。如果被轉(zhuǎn)換的日期已經(jīng)是當(dāng)前周中的指定日期，不做任何修改。例如

-- 返回當(dāng)前年份中十月第一個(gè)星期二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 個(gè)修飾符（unixepoch）只能修改 DDDDDDDDDD 格式的時(shí)間值。該修飾符將 DDDDDDDDDD 轉(zhuǎn)換為 Unix 時(shí)間戳（1970 年以來的秒數(shù)），而不是通常情況下的儒略日天數(shù)。例如：

-- 返回 Unix 時(shí)間戳 1092941466 對應(yīng)的日期和時(shí)間
sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

如果 unixepoch 修飾符前面不是 DDDDDDDDDD 格式的時(shí)間值，或者它們之間存在其他修飾符，轉(zhuǎn)換的結(jié)果不可預(yù)期。

第 12 個(gè)修飾符（localtime）將左側(cè)的 UTC 時(shí)間值轉(zhuǎn)換為本地時(shí)區(qū)對應(yīng)的時(shí)間值。如果左側(cè)的時(shí)間值不是 UTC 時(shí)區(qū)，轉(zhuǎn)換的結(jié)果不可預(yù)期。例如：

-- 計(jì)算 Unix 時(shí)間戳 1092941466 對應(yīng)的時(shí)間，并且轉(zhuǎn)換為本地時(shí)間
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-20 02:51:06


第 13 個(gè)修飾符（utc）執(zhí)行的轉(zhuǎn)換操作和 localtime 正好相反，將左側(cè)的本地時(shí)間轉(zhuǎn)化為 UCT 時(shí)間。如果左側(cè)的時(shí)間值不是本地時(shí)間，轉(zhuǎn)換的結(jié)果不可預(yù)期。