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