HN公開: SQLiteの高精度な日付/時刻機能

(antonz.org)

2 ポイント投稿者 GN⁺ 2024-08-16 | 1件のコメント | WhatsAppで共有

高精度な日付/時刻拡張機能

SQLiteは基本的な日付関数を提供しているが、さらに多くの機能が必要だったため、sqlean-timeという高精度な日付/時刻拡張機能が作られた。この拡張は、構造化されたAPIと多様な機能を提供する。

参考. SQLiteに拡張を追加するのは非常に簡単。ファイルをダウンロードして、1つのデータベースコマンドを実行するだけでよい。

概念

この拡張は2種類の値型を使用する: 時刻（Time）と期間（Duration）。

時刻（Time）: 秒とナノ秒で構成されたペアで、0時刻（0001-01-01 00:00:00 UTC）以降の秒数と、現在の秒内のナノ秒を表す。
- 内部表現として時刻を保存でき、これは数十億年前後の日付をナノ秒精度で表現できる。
- Unix epoch（1970-01-01 00:00:00 UTC）以降の秒数（ミリ秒、マイクロ秒、ナノ秒）として時刻を保存することもできる。
- 時刻は常にUTCで保存および処理されるが、特定のタイムゾーンオフセットへ変換可能。
期間（Duration）: ナノ秒単位の64ビット数値で、約290年までの値を表せる。

時刻値の生成

現在時刻:

select time_fmt_iso(time_now());  -- 2024-08-06T21:22:15.431295000Z

特定の日付/時刻:

select time_fmt_iso(time_date(2011, 11, 18));  -- 2011-11-18T00:00:00Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));  -- 2011-11-18T15:56:35Z

時刻フィールドの抽出

さまざまな日付/時刻フィールドを抽出する関数がある:

select 'year  = ' || time_get_year(time_now());
select 'month  = ' || time_get_month(time_now());
select 'day   = ' || time_get_day(time_now());

Unix時間

Unix時間（1970-01-01 UTC以降の時刻）から時刻値を生成する関数:

select time_fmt_iso(time_unix(1321631795));  -- 2011-11-18T15:56:35Z

時刻値をUnix時間へ変換する関数:

select time_to_unix(time_now());  -- 1722979335

時刻の比較

時刻値を比較する関数:

select time_after(time_now(), time_date(2011, 11, 18));  -- 1
select time_before(time_now(), time_date(2011, 11, 18));  -- 0

時刻演算

期間を時刻値に加算する関数:

select time_fmt_iso(time_add(time_now(), 24*dur_h()));  -- 2024-08-07T21:22:15.431295000Z

期間定数:

dur_us() - 1マイクロ秒
dur_ms() - 1ミリ秒
dur_s() - 1秒
dur_m() - 1分
dur_h() - 1時間

丸め

指定したフィールドの精度に時刻値を丸める関数:

select 'original  = ' || time_fmt_iso(t.v) from t union all
select 'millennium = ' || time_fmt_iso(time_trunc(t.v, 'millennium')) from t;

フォーマット

ISO 8601時刻文字列を返す関数:

select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888), 3*3600);  -- 2011-11-18T18:56:35.666777888+03:00

期間定数

一般的な期間をナノ秒で返す関数:

select dur_ns();  -- 1
select dur_us();  -- 1000

謝辞

この拡張はCで実装されており、Goの標準ライブラリのtimeパッケージ（BSD 3-Clause License）をベースに設計・実装されている。

インストールと使い方

最新リリースをダウンロード
SQLiteコマンドラインインターフェースで使用:
```
sqlite> .load ./time
sqlite> select time_now();
```

GN⁺のまとめ

sqlean-time拡張はSQLiteに高精度な日付/時刻機能を追加し、多様な時刻演算を可能にする。
時刻と期間をナノ秒単位で扱えるため、非常に高精度な時刻計算が可能。
多様な時刻フォーマットおよび比較機能を提供し、開発者が容易に利用できる。
SQLiteの基本日付関数よりはるかに多くの機能を提供するため、複雑な時刻演算を必要とするプロジェクトに有用。

1件のコメント

GN⁺ 2024-08-16

Hacker Newsのコメント

Jon Skeetが文書化したタイムゾーンの変更やローカル時刻の不連続性といった特殊なケースを処理するのか、という質問
- 関連リンク: Stack Overflow
- Computerphileによる10分間の動画解説へのリンク: YouTube
日付/時刻や暗号化ライブラリを自前で構築しないほうがよい
- 無限にあるエッジケースが問題を引き起こしうる
- 新しいライブラリに懐疑的になる理由
3つの異なる時間表現/サイズが興味深い
- ナノ秒精度が数十億年の期間にわたって必要になるユースケースが気になる
- ナノ秒精度では ±290年の範囲しか提供されないのが混乱を招く
符号付き整数を使っているかどうかを明確にすることが重要
- 文書を読むと、符号付き整数かもしれないし、そうでないかもしれない
- 符号付き整数なら、同じ日付と時刻を表す複数のビット列がありうる
SQLite3に拡張可能な型システムがあればよいのにと思う
SQLiteの重要だが欠けている機能に触れつつ、とてもクールだと評価している
データベースは単位を追跡すべきだと主張
- たとえば、時刻カラムが float64 の秒単位で表されることを明示できるべき
- データベースが "2h" を 7200.0 秒に変換し、テーブルスキャン中に比較できるべき
- 以前、そのような単位処理を行う特化型SQLデータベースを書いたことがあるが、それ以来見たことがない
- 時間だけでなく、質量、体積、情報量、温度など、あらゆる単位を扱えるべき
- 数学的な誤りを早期に捕捉できるよう、数学的に無意味な演算をデータベースが拒否するよう教えられるはず
ナノ秒表現と、ナノ秒の範囲外の年のどちらがより有用かという質問
- 「正確な」科学をしているわけではないので、ナノ秒の価値は限定的
- 歴史的な日付を表現できるほうが、より頻繁に必要になりそう
nanosecond単位のGolangスタイルのUnixタイムスタンプを、符号付き int64 で使うことを提案
- ナノ秒精度では数百万年をカバーできないかもしれないが、本当に必要なのか疑問
「epoch以降の秒」という表現は、正確にその意味でない限り使うべきではないと主張
- 例のクエリ: select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));