SQLite 時間,時區問題

TL;DR

SQLite 並沒有辦法設定時區,儲存的時間都是使用 UTC 時間,新增和編輯時間時最好是先將時間轉換為 UTC 時間,讀取時將 UTC 時間轉換為本地時間再輸出。

建立個測試資料表 test_table ,並且讓 created_at 欄位能夠自動產生時間
    
create table test_table
(
    name       varchar(255)                        not null,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP not null
);
    

增加三筆測試資料:
    
-- 使用系統時間
insert into test_table (name, created_at)
values ('datetime', datetime());

-- 欄位自動產生時間
insert into test_table (name)
values ('default');

-- 手動指定時間
insert into test_table (name, created_at)
values ('2023-01-29 23:00:00', '2023-01-29 23:00:00');
    

當前電腦時間為 23 時,時區 UTC+8(GMT+8),新增完畢後的資料如下:
    
| name                | created_at          |
|:--------------------|:--------------------|
| datetime            | 2023-01-29 15:00:00 |
| default             | 2023-01-29 15:00:00 |
| 2023-01-29 23:00:00 | 2023-01-29 23:00:00 |
    

可以發現除了手動指定時間的那一筆,其他的時間被自動轉換為 UTC 時間了!

查詢的時候查到的是 UTC 時間,那要怎麼轉換為本地時間呢?可以使用下面的 sql 語法:
    
select name, created_at, datetime(created_at, 'localtime') as local_created_at
from test_table;
    

執行結果:
    
| name                | created_at          | local_created_at    |
|:--------------------|:--------------------|:--------------------|
| datetime            | 2023-01-29 15:00:00 | 2023-01-29 23:00:00 |
| default             | 2023-01-29 15:00:00 | 2023-01-29 23:00:00 |
| 2023-01-29 23:00:00 | 2023-01-29 23:00:00 | 2023-01-30 07:00:00 |
    

嗯嗯,很棒,時間對了...咦,等等!最後面手動指定時間的那筆錯了啊!怎麼多幫我加了八小時?!

所以新增時應該將時間轉換為 UTC 時間再新增,讀取時將 UTC 轉換為 local 時間再輸出:
    
-- 先將時間轉換為 utc 時間再新增
insert into test_table (name, created_at)
values ('2023-01-29 23:00:00', datetime('2023-01-29 23:00:00', 'utc') );
    

這樣讀取時轉換為本地時間時就會正常了!
    
| name                | created_at          | local_created_at    |
|:--------------------|:--------------------|:--------------------|
| datetime            | 2023-01-29 15:00:00 | 2023-01-29 23:00:00 |
| default             | 2023-01-29 15:00:00 | 2023-01-29 23:00:00 |
| 2023-01-29 23:00:00 | 2023-01-29 15:00:00 | 2023-01-29 23:00:00 |
    


參考資料:
SQLite - Date And Time Functions

留言