你的資料庫支援時間資料型別嗎？

本文轉載自 Leon 的網誌

「資料庫支援時間資料型別嗎？」這好像不應該是個問題，但是最近在普查資料庫系統時，才意識到這的確是該被考慮的問題之一，特別是在 NoSQL 系的資料庫，受限於 JSON 原生沒有日期與時間型態的先天限制，各家 NoSQL 資料庫系統對時間與日期的處理方式也因此花招百出。

為什麼日期、時間要用特有的資料型態？

資料庫做為儲存巨量資料的系統，提供了一些通用或特有的查詢語言讓我們與其溝通，像是 SQL 語言就是通用於 FireBird、PostreSQL、MariaDB、SQLite、MySQL 的查詢語言，儘管他們之間有著若干的語法差異，但對一般查詢工作上是沒有太大影響的，資料庫系統在做出查詢之後會回覆查詢結果。

對於一個有支援日期與時間型態的資料庫系統，我們可以直接在 SQL 寫出針對時間的查詢條件，例如一個存有數十萬筆交易紀錄的表中調出 3/2 號下午兩點到三點的交易，這樣的時間區間會成為資料庫系統的查詢條件之一，也因此它只會回覆符合查詢區間內的那些交易紀錄，反之若是資料庫系統不支援日期、時間型態，也就是說像這樣的時間紀錄 2021-03-02T14:13:14.567Z 資料庫系統看不懂，只能當它是一組無意義的字串，那麼前面的例子就只能改成讓資料庫系統回覆全部數十萬筆的資料，再用自己寫的程式去把這巨量資料做二次加工篩選挑出我們要的那一小時交易紀錄，這樣的機制顯然是不現實的，因此資料庫系統在設計上必然得支援時間、日期型態，或者是必須提供其它的方式讓我們可以針對時間做出查詢。

那些不支援時間資料型態的資料庫系統

SQLite

SQL 系的資料庫系統大多數都支援日期與時間資料型態，唯一的例外是 SQLite，SQLite 並不在欄位層級支援時間、日期資料型態，時間或日期在表格內是以字串型態被儲存，SQLite 是以時間或日期相關函式的方式讓我們對查詢下出時間條件，例如下面查詢自 2014 某刻至今的秒數：

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Deta Base

Deta Base 是個雲端的 NoSQL 服務，之前曾經在〈各家 Serverless 短評〉介紹過 Deta 的另一個 serverless 服務 Deta Micros。

Deta Base 有提供 Python、JavaScript 的套件以及 REST API 的方式做查詢，但不論是何種方式都必須把程式語言的時間或日期物件轉換成字串包進 JSON 內，才能打進 Deta Base，Deta Base 本身也不認得時間，不像 SQLite 還有提供函式可以做處理，而 Deta 建議的處理方式是把時間轉換成 Unix 時間，以數字儲存，也因此可以做出加減運算，相當 geek：

Deta do not have native date / time types.

一段典型的 Unix 時間會是這樣：1616670631，而因為要做運算 Deta Base 要我們用整數型態去存 Unix 時間，但 Deta Base 並未告知他們的整數是 int32 或 int64，有可能有 2038 年問題的風險。

Backendless Database

Backendless 顧名思義是提供一系列 serverless 後端的服務，有點像 Firebase，近來 Backendless 也開始擴展服務項目往 codeless 應用開發領域發展，Backendless Database 則是它們家的資料庫服務。

Backendless 提供的套件有 Flutter、Swift、Android、Java、.NET、JavaScript，也有提供 REST API 接口，Backendless Database 具有 NoSQL 的特性 schema-less，也就是說不需要先行定義表格（在 NoSQL 的世界通常稱為 collection）內的欄位與型態，每次丟到同一個表的欄位也可以長不一樣，在 Backendless Database 裡，最終表格的欄位會是所有紀錄的欄位的聯集，例如第一次丟的 JSON 是長這樣：

{
    "title": "Saving Single Object",
    "link_url": "https://backendless.com/docs/rest/data_single_object_create.html",
    "score": 100,
    "timestamp2": "05/20/2016 03:58:23",
}

而第二次你對同一個表格丟的 JSON 卻長這樣：

{
    "title": "Client-side Setup",
    "link_url": "https://backendless.com/docs/js/setup.html",
    "rank": 100,
    "timestamp2": "05/20/2016 03:58:23",
}

那麼這張表聯集完的欄位是長這樣：

{
    "title": "Client-side Setup",
    "link_url": "https://backendless.com/docs/js/setup.html",
    "rank": 100,
    "score": null,
    "timestamp2": "05/20/2016 03:58:23",
}

當然實際上不太可能出現上面 score、rank 傻傻分不清的離譜狀況。

Schema-less 特性帶來的是省去 ALTER TABLE 的操作，不再需要 ORM 的 migration 機制。

但如同上面的例子 REST API 接受的內容格式是 JSON，JSON 的原始定義內是沒有日期與時間的資料型態的，必須轉為字串，因此 Backendless 那端收到的也視為字串處理。

想要定義正確的時間型態，得在 Backendless 的 schema editor 先定義好欄位的型態：

Backednless schema editor

雖然前面說過 NoSQL 的 schema-less 特性，但其實還是可以定義欄位型態的，只是這動作變成非必要的，但在 Backendless Database 我們為了正確的指定欄位的時間型態，卻又必須「回歸正道」的設定欄位型態，這樣做等於放棄了 schema-less 的特性，並且 Backendless 也沒有提供透過 API 定義欄位型態的方式，最終這在 Backendless 變成一種比 migration 還不方便的機制，爾等必須手動去做每一次相當於 ALTER TABLE 的操作。

但即便這樣一波帥氣的手動操作完了，丟上去的 DATETIME 資料，再透過 REST API 傳回來，又變成 Unix 時間…：

{
    "score": 100.0,
    "rank": null,
    "created": 1616721085418,
    "link_url": "https://backendless.com/docs/rest/data_single_object_create.html",
    "ownerId": null,
    "title": "Saving Single Object",
    "objectId": "2D0BBC30-5D01-4015-8CA3-2B4CC3BF36A1",
    "timestamp": 1463716703000
}

注意到不論是我自己加的欄位 timestamp 或 Backendless 預帶的 created 都是整數型態的 Unix 時間。

追根究底還是回到 JSON 的老問題，JSON 就是沒有時間日期的資料型態，因此只能使出各種奇技淫巧來應付日期時間的需求，特別是在廣泛使用 JSON 格式的 REST API 這樣的問題就更加突顯，如果是不用 REST API，而是用 Backendless 提供的語言套件，那或許可以直接原生支援該語言的時間物件轉換成 Backendless 的時間型態，但這也意味著語言的選擇受限，或者走 REST API 就必須多一層 workaround 的功夫。

那些繞過 JSON 不支援時間型態問題的資料庫系統

JSON 不支援時間、日期資料型態顯然是個問題，那些 NoSQL 系統也注意到了，因此有了這幾種典型解法：

不用 JSON，改用 BSON。BSON 是 binary JSON 的意思，除了變成二進位容量更小傳輸更快外，BSON 也支援日期與時間的資料型態，採用 BSON 最有代表性的就是 NoSQL 的老大 MongoDB，並且 MongoDB 的 API 套件也封裝的很優雅，程式語言原生的日期型態會被 MongoDB 套件自動地轉換成 BSON 的日期型態，這一切都是隱式發生的。
擴充 JSON，讓 JSON 支援時間型態，RethinkDB 與 Fauna 就是這麼做的，他們的語言套件也類似 MongoDB，能幫我們自動的轉換日期、時間型態。
用 Unix 時間，其他都不管了，如同前面提到的，這也算是一招。
像 SQLite 那樣用函式處理日期字串，除了 SQLite 外，還有 Couchbase 也是用這招。

那麼上面這四招，哪招用起來最舒服呢？個人是愛前兩種，他們都提供封裝優雅的語言套件，自動的幫我們做轉換，讓我們不用煩惱底層的資料型態問題。