SQLite 中的日期和時間
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學,十多年數(shù)據庫管理與開發(fā)經驗,目前在一家全球性的金融公司從事數(shù)據庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目錄
日期和時間類型
日期和時間函數(shù)
指定時間值
指定修飾符
今天我們來詳細介紹一下 SQLite 中的日期和時間數(shù)據類型,以及相關的處理函數(shù)。
日期和時間類型
首先,SQLite 使用一種動態(tài)的數(shù)據類型系統(tǒng),數(shù)據的類型只和數(shù)據本身有關,與字段定義的類型無關。例如,我們可以定義一個 INTEGER 類型的字段,然后用于存儲字符串。這一點和其他數(shù)據庫不同,通常數(shù)據庫字段的類型決定了可以存儲的數(shù)據類型。
其次,SQLite 沒有提供專門用于存儲日期和時間的數(shù)據類型。通常我們可以使用 TEXT、REAL 或者 INTEGER 存儲日期和時間,SQLite 提供了支持這些數(shù)據類型的日期和時間函數(shù)。
TEXT 存儲日期和時間時使用 ISO8601 格式的字符串(“YYYY-MM-DD HH:MM:SS.SSS”)。
REAL 存儲日期時表示儒略日(Julian Day),也就是格里歷(陽歷)公元前 4714 年 11 月 24 日 UTC 正午到該時間的天數(shù)。
INTEGER 存儲日期和時間時表示 Unix 時間戳,也就是 1970-01-01 00:00:00 UTC 到該時間的秒數(shù)。
應用程序可以選擇以上三種格式之一存儲日期和時間,SQLite 內置函數(shù)可以支持它們之間的格式轉換。
日期和時間函數(shù)
SQLite 提供了以下 5 種日期和時間函數(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ù)都可以接收一個時間值作為參數(shù),后面可以增加任意多個修飾符。strftime() 函數(shù)的第一個參數(shù)用于指定格式化字符串。
日期和時間函數(shù)使用 IS0-8601 日期和時間格式的一個子集。
date() 函數(shù)返回的日期格式為 YYYY-MM-DD,例如:
sqlite> select date();
2021-08-06
time() 函數(shù)返回的時間格式為 HH:MM:SS,例如:
sqlite> select time();
03:57:53
datetime() 函數(shù)返回的日期時間格式為 YYYY-MM-DD HH:MM:SS,例如:
sqlite> select datetime();
2021-08-06 03:58:39
julianday() 函數(shù)返回儒略日的天數(shù),包括時間信息。例如:
sqlite> select julianday(), datetime();
2459432.66719566|2021-08-06 04:00:45
strftime() 函數(shù)返回日期和時間的格式由第一個參數(shù)中的格式化字符串決定。格式化字符串支持標準 C 代碼庫中最常用的控制符以及 %f 和 %J。以下是一個完整的 strftime() 控制符列表:
%d 一月中的第幾天:00-31
%f 包含毫秒的秒數(shù):SS.SSS
%H 小時: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
實際上其他 4 個函數(shù)都可以使用 strftime() 函數(shù)表示:
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
提供這些函數(shù)主要是為了方便使用,提高效率。
指定時間值
時間值參數(shù)可以使用以下格式之一。時間值通常是一個字符串,只有在第 12 種格式時可以指定一個整數(shù)或者浮點數(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
例如:
-- 返回當前日期
SELECT date('now');
-- 返回當前 Unix 時間戳
SELECT strftime('%s','now');
-- 計算 2000 年以來的天數(shù)
SELECT julianday('now') - julianday('2000-01-01');
-- 計算 2000 年以來的秒數(shù)
SELECT strftime('%s','now') - strftime('%s','2000-01-01 00:00:00');
-- 計算 Unix 紀元以來的秒數(shù),類似于 strftime('%s','now'),但是包含小數(shù)秒
SELECT (julianday('now') - 2440587.5)*86400.0;
第 5 種到第 7 種格式中的字符 T 用于分隔日期和時間,遵循 ISO-8601 標準。
第 8 種到第 10 種格式只包含時間信息,默認日期為 2000-01-01。
第 11 種格式中的字符串“now”表示當前日期和時間,時區(qū)為 UTC。
第 12 種格式是儒略日的天數(shù),支持整型和浮點型數(shù)字值。
第 2 種到第 10 種格式可以增加一個時區(qū)標識符“[±]HH:MM”或者“Z”。日期和時間函數(shù)在內部使用 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 種格式中,小數(shù)秒 SS.SSS 可以包含一個或多個小數(shù)位數(shù)。示例中只顯示了 3 位小數(shù),因為只有前三位小數(shù)是有效數(shù)字,不過輸入字符串中可以包含更少或更多小數(shù),日期/時間函數(shù)可以正確處理這些情況。同樣,第 12 中格式顯示了 10 位數(shù)字,但是日期/時間函數(shù)可以接收能夠表示為儒略日天數(shù)的任意位數(shù)的數(shù)字。
指定修飾符
時間值參數(shù)的后面可以增加任意多個修飾符,用于改變日期和時間的數(shù)值。每個修飾符都會對其左側的時間進行轉換,修飾符從左至右依次應用。以下是可用的修飾符:
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 月份,最后規(guī)范化結果。例如,日期 2001-03-31 使用修飾符 ‘+1 month’ 時首先產生 2001-04-31,由于 4 月份只有 30 天,因此最終規(guī)范化的結果為 2001-05-01。類似,如果原始數(shù)據是閏年中的 2 月 29 日,修飾符是 ±N years 并且 N 不是 4 的倍數(shù),也會產生同樣的效果。
第 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 年以來的秒數(shù)),而不是通常情況下的儒略日天數(shù)。例如:
-- 返回 Unix 時間戳 1092941466 對應的日期和時間
sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
如果 unixepoch 修飾符前面不是 DDDDDDDDDD 格式的時間值,或者它們之間存在其他修飾符,轉換的結果不可預期。
第 12 個修飾符(localtime)將左側的 UTC 時間值轉換為本地時區(qū)對應的時間值。如果左側的時間值不是 UTC 時區(qū),轉換的結果不可預期。例如:
-- 計算 Unix 時間戳 1092941466 對應的時間,并且轉換為本地時間
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-20 02:51:06
第 13 個修飾符(utc)執(zhí)行的轉換操作和 localtime 正好相反,將左側的本地時間轉化為 UCT 時間。如果左側的時間值不是本地時間,轉換的結果不可預期。