SQLite 中常用日期和時間函數及案例
準備工作
首先準備一個 DB 文件，裏面創建一張訂單表work_order，建表語句如下：
CREATE TABLE "work_order" (
"order_id" REAL,
"order_status" REAL,
"order_time" TEXT,
"consign_status" REAL,
"consign_time" TEXT,
"pay_time" TEXT,
"post_cost" REAL,
"modified" REAL,
"created" REAL
);表字段說明：
向表裏插入數據：
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7255166.0, 110.0, '2024-04-08 21:26:25', NULL, '1711814608000', '2024-04-08 21:26:34', 8.4, '2024-04-17 00:14:37', '2024-04-08 21:27:00');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7255497.0, 5.0, '2024-04-08 21:54:48', NULL, '1712029698000', '2024-04-08 21:54:49', 0.0, '2024-04-17 00:56:48', '2024-04-08 21:55:05');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7258422.0, 110.0, '2024-04-09 09:52:19', NULL, '1712419383000', '2024-04-09 09:52:25', 4.4, '2024-04-17 00:30:37', '2024-04-09 09:52:36');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7264310.0, 110.0, '2024-04-09 19:58:21', NULL, '1712419386000', '2024-04-09 19:58:29', 10.3, '2024-04-17 00:47:37', '2024-04-09 19:58:39');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7266348.0, 110.0, '2024-04-09 23:19:35', NULL, '1712419421000', '2024-04-09 23:21:05', 5.3, '2024-04-17 00:11:36', '2024-04-09 23:20:14');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7270269.0, 110.0, '2024-04-10 13:11:28', NULL, '1712419413000', '2024-04-10 13:11:29', 4.4, '2024-04-17 00:50:57', '2024-04-10 13:11:46');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7270366.0, 110.0, '2024-04-10 13:17:06', NULL, '1712419420000', '2024-04-10 13:19:12', 10.0, '2024-04-17 00:32:39', '2024-04-10 13:18:42');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7274377.0, 110.0, '2024-04-10 20:18:06', NULL, '1712419418000', '2024-04-10 20:18:14', 8.4, '2024-04-17 00:24:51', '2024-04-10 20:18:36');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7282565.0, 110.0, '2024-04-11 17:18:26', NULL, '1712419423000', '2024-04-11 17:18:28', 6.2, '2024-04-17 00:00:37', '2024-04-11 17:18:42');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7286332.0, 110.0, '2024-04-11 23:05:13', NULL, '1712419401000', '2024-04-11 23:05:15', 6.2, '2024-04-17 00:42:32', '2024-04-11 23:05:32');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7286596.0, 110.0, '2024-04-11 23:29:25', NULL, '1712419456000', '2024-04-11 23:30:57', 13.9, '2024-04-17 00:05:38', '2024-04-11 23:30:17');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7287060.0, 110.0, '2024-04-12 00:25:50', NULL, '1712419405000', '2024-04-12 00:25:54', 7.7, '2024-04-17 00:28:37', '2024-04-12 00:26:13');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7289443.0, 110.0, '2024-04-12 10:41:37', NULL, '1712419453000', '2024-04-12 10:41:42', 7.5, '2024-04-17 00:47:37', '2024-04-12 10:44:37');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7292592.0, 110.0, '2024-04-12 15:49:23', NULL, '1712419461000', '2024-04-12 15:49:30', 7.5, '2024-04-17 00:50:37', '2024-04-12 15:49:43');
INSERT INTO "work_order" ("order_id", "order_status", "order_time", "consign_status", "consign_time", "pay_time", "post_cost", "modified", "created") VALUES (7292970.0, 110.0, '2024-04-12 16:30:55', NULL, '1712419452000', '2024-04-12 16:30:57', 4.4, '2024-04-17 00:26:38', '2024-04-12 16:31:15');準備好以上數據後，就可以跟着來看如下幾個案例了。
案例 1: date 函數的使用
date() 函數用來處理日期，以文本格式返回日期：YYYY-MM-DD。
比如，要查詢支付日期爲 2024-04-12 這天的訂單，利用 date() 函數編寫 SQL 來實現：
SELECT * FROM work_order WHERE DATE(pay_time)='2024-04-12';執行結果：
order_id order_status order_time consign_status consign_time pay_time post_cost modified created
7287060 110 2024-04-12 00:25:50 1 1712419405000 2024-04-12 00:25:54 7.7 2024-04-17 00:28:37 2024-04-12 00:26:13
7289443 110 2024-04-12 10:41:37 11 1712419453000 2024-04-12 10:41:42 7.5 2024-04-17 00:47:37 2024-04-12 10:44:37
7292592 110 2024-04-12 15:49:23 1 1712419461000 2024-04-12 15:49:30 7.5 2024-04-17 00:50:37 2024-04-12 15:49:43
7292970 110 2024-04-12 16:30:55 1 1712419452000 2024-04-12 16:30:57 4.4 2024-04-17 00:26:38 2024-04-12 16:31:15案例 2: time 函數的使用
time() 函數用來處理時間，以文本格式返回時間：HH:MM:SS。
比如，要查詢更新時間爲 00:00:00~00:30:00 時間範圍內的訂單，使用 time() 函數實現：
SELECT * FROM work_order WHERE TIME(modified)>='00:00:00' AND TIME(modified)<='00:30:00';執行結果：
order_id order_status order_time consign_status consign_time pay_time post_cost modified created
7255166 110 2024-04-08 21:26:25 1 1711814608000 2024-04-08 21:26:34 8.4 2024-04-17 00:14:37 2024-04-08 21:27:00
7266348 110 2024-04-09 23:19:35 1 1712419421000 2024-04-09 23:21:05 5.3 2024-04-17 00:11:36 2024-04-09 23:20:14
7274377 110 2024-04-10 20:18:06 1 1712419418000 2024-04-10 20:18:14 8.4 2024-04-17 00:24:51 2024-04-10 20:18:36
7282565 110 2024-04-11 17:18:26 1 1712419423000 2024-04-11 17:18:28 6.2 2024-04-17 00:00:37 2024-04-11 17:18:42
7286596 110 2024-04-11 23:29:25 1 1712419456000 2024-04-11 23:30:57 13.9 2024-04-17 00:05:38 2024-04-11 23:30:17可以看到倒數第二列的值就是更新時間，這些值都在我們要求的範圍內。
案例 3: datetime 函數的使用
datetime() 函數用於處理比較具體的時間，以相同格式的文本形式返回日期和時間：YYYY-MM-DD HH:MM:SS。
通常用於定位比較精確的時間點，比如訂單中的發貨時間是毫秒級時間戳文本，看起來不那麼直觀，如果想更清楚的看到發貨時間，可以用 datetime() 函數來進行轉換一下：
SELECT order_id,order_status,post_cost,DATETIME(CAST(consign_time AS INTEGER)/1000, 'unixepoch') AS send_time,consign_status
FROM work_order;執行結果：
order_id order_status post_cost send_time consign_status
7255166 110 8.4 2024-03-30 16:03:28 1
7255497 5 0 2024-04-02 03:48:18 1
7258422 110 4.4 2024-04-06 16:03:03 1
7264310 110 10.3 2024-04-06 16:03:06 1
7266348 110 5.3 2024-04-06 16:03:41 1
7270269 110 4.4 2024-04-06 16:03:33 1
7270366 110 10 2024-04-06 16:03:40 1
7274377 110 8.4 2024-04-06 16:03:38 1
7282565 110 6.2 2024-04-06 16:03:43 1
7286332 110 6.2 2024-04-06 16:03:21 1
7286596 110 13.9 2024-04-06 16:04:16 1
7287060 110 7.7 2024-04-06 16:03:25 1
7289443 110 7.5 2024-04-06 16:04:13 11
7292592 110 7.5 2024-04-06 16:04:21 1
7292970 110 4.4 2024-04-06 16:04:12 1'consign_time' 字段值是毫秒級的時間戳，CAST('consign_time' AS INTEGER) 將這個文本時間戳轉爲 INTEGER 格式，然後再除以 1000 轉爲秒級時間戳，再通過 datatime 函數將其轉爲時間戳格式。
案例 4: strftime 函數的使用
strftime() 函數用得比較廣泛，它返回根據指定爲第一個參數的格式字符串格式化的日期。它能實現前面提到的幾個函數。
比如，我們表裏的發貨時間 consign_time 字段值爲毫秒級的時間戳文本，現在要按發貨時間來查詢 2024-04-06 這天的訂單，就可以使用 strftime 來實現：
SELECT order_id,order_status,post_cost,DATETIME(CAST(consign_time AS INTEGER)/1000, 'unixepoch') AS send_time,consign_status
FROM work_order
WHERE STRFTIME('%Y-%m-%d', DATETIME(CAST(consign_time AS INTEGER) / 1000, 'unixepoch'))='2024-04-06';在上一個案例的基礎上，將查詢中的 DATETIME(CAST(consign_time AS INTEGER)/1000, 'unixepoch') 作爲 strftime 函數的參數，第一個參數是日期的格式，且必須放在第一個位置，最後返回一個給的格式的日期文本，然後與給定的日期比較。
好了，以上便是對 SQLite 中常用時間日期函數的介紹，多實踐才能更靈活的應用。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/0xjBvWhRo6A6wH2n_7qNww