SQLiteで高精度な日時を扱う：sqlean-time

 (antonz.org)
2 ポイント 投稿者 GN⁺ 2024-08-16 | 1件のコメント | WhatsAppで共有
  • SQLite標準の日付関数では不足する場合、sqlean-timeはナノ秒精度のTime・Duration型と日時関数を拡張として追加する
  • Time値は0001-01-01 00:00:00 UTC以降の秒数と現在の秒内のナノ秒で構成され、13バイトBLOBで保存すると過去・未来の数十億年の範囲を扱える
  • Unix epoch基準の64ビットNUMBERでの保存も可能だが、単位が小さくなるほど範囲は狭まり、ナノ秒単位では1678年から2262年までしか表現できない
  • APIは生成、フィールド抽出、Unix time変換、比較、算術、切り捨て・丸め、ISO 8601フォーマット・パースを含み、値は常にUTCで保存・演算される
  • カレンダー計算はグレゴリオ暦を前提とし、うるう秒は扱わないため、日・月・年の計算にはDurationベースのtime_add()ではなくtime_add_date()を使う必要がある

sqlean-timeの時間モデル

  • sqlean-timeはSQLiteに高精度な日時処理を追加する拡張である
  • SQLite拡張はファイルをダウンロードし、データベースコマンドを1つ実行することで追加できる
  • この拡張は2種類の値を中心に動作する
    • Time：特定の時刻
    • Duration：期間

Timeの表現と保存範囲

  • Timeは(seconds, nanoseconds)のペアで構成される
    • seconds：zero timeである0001-01-01 00:00:00 UTC以降の秒数を表す64ビット整数
    • nanoseconds：現在の秒内のナノ秒値で、範囲は0-999999999
  • 最大限の柔軟性が必要な場合、Time値を内部表現である13バイトBLOBとして保存できる
    • この方式は、過去と未来の数十億年の日時をナノ秒精度で表現する
  • Unix epochである1970-01-01 00:00:00 UTC以降の秒、ミリ秒、マイクロ秒、ナノ秒を64ビット整数NUMBERとして保存する方式もサポートする
    • 秒：過去・未来の数十億年を秒精度で表現
    • ミリ秒：1970年の前後2億9200万年をミリ秒精度で表現
    • マイクロ秒：-290307年から294246年まで表現
    • ナノ秒：1678年から2262年まで表現
  • Timeは常にUTCで保存・演算される
    • 特定のタイムゾーンオフセットとの変換は可能
  • カレンダー計算は常にグレゴリオ暦を仮定する
    • うるう秒は使用しない

Durationと値の生成

  • Durationはナノ秒単位の64ビット整数である
    • 約290年までの期間を表現できる
    • NUMBERとして保存できる
  • 現在時刻はtime_now()で作成できる
    • 例：time_fmt_iso(time_now())は2024-08-06T21:22:15.431295000ZのようなISO文字列を返す
  • 特定の日時はtime_date()で生成する
    • 日付だけを指定するとUTCの午前0時になる
    • 時・分・秒とナノ秒をあわせて指定できる
    • タイムゾーンオフセットを渡すとUTC時刻に変換される

日時フィールドの抽出

  • 個別フィールド抽出関数は、年、月、日、時、分、秒、ナノ秒、曜日、年内通算日、ISO年、ISO週番号を返す
    • 例：time_get_year()、time_get_month()、time_get_day()、time_get_hour()、time_get_minute()、time_get_second()、time_get_nano()
  • 汎用関数time_get()は、フィールド名の文字列で値を抽出する
    • サポート例：millennium、century、decade、year、quarter、month、day
    • 時間単位の例：hour、minute、second、milli、micro、nano
    • ISO・カレンダー関連の例：isoyear、isoweek、isodow、yearday、weekday
    • Unix epoch値はepochで取得できる

Unix time変換

  • Unix timeからTime値を作成する関数が提供される
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Time値をUnix timeに戻す関数もある
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Unix系OSは時間を32ビット秒値として記録する場合が多いが、time_to_unix()は64ビット値を返す
    • 過去・未来の数十億年の範囲で有効
    • time_to_milli()は1970年の前後2億9200万年まで表現する
    • time_to_micro()は-290307年から294246年まで表現する
    • time_to_nano()は1678年から2262年まで表現する

比較と算術

  • 時刻比較関数は、2つのTime値の順序を判定する
    • time_after()：最初の時刻が2番目の時刻より後かどうかを返す
    • time_before()：最初の時刻が2番目の時刻より前かどうかを返す
    • time_compare()：後なら1、前なら-1、同じなら0を返す
    • time_equal()：2つの値が同じ時刻を表すかどうかを返す
  • time_add()はTime値にDurationを加算する
    • 負のDurationを使えば減算できる
    • dur_us()、dur_ms()、dur_s()、dur_m()、dur_h()のようなDuration定数をあわせて使える
  • 日・月・年を加算するときは、time_add()ではなくtime_add_date()を使う必要がある
    • time_add_date()は年、月、日を加算し、負の値で減算できる
  • time_sub()は2つのTime値の間の期間をナノ秒で返す
  • time_since()は指定した時刻以降に経過した時間をナノ秒で返す
  • time_until()は指定した時刻までの残り期間をナノ秒で返す

切り捨てと丸め

  • time_trunc()はTime値を指定したフィールド精度まで切り捨てる
    • サポート例：millennium、century、decade、year、quarter、month、week、day、hour、minute、second、milli、micro
    • 例えば2011-11-18T15:56:35.666777888Zをhourで切り捨てると2011-11-18T15:00:00Zになる
  • 指定したDurationの倍数でも切り捨てられる
    • 例：12*dur_h()、dur_h()、30*dur_m()、dur_m()、30*dur_s()、dur_s()
  • time_round()は指定したDurationの最も近い倍数に丸める
    • 例：2011-11-18T15:56:35.666777888Zをdur_h()で丸めると2011-11-18T16:00:00Zになる
    • 同じ値をdur_s()で丸めると2011-11-18T15:56:36Zになる

フォーマットとパース

  • time_fmt_iso()はTime値をISO 8601文字列として返す
    • タイムゾーンオフセットを任意で受け取り、そのオフセットに変換したうえでフォーマットできる
    • ナノ秒がある値は2011-11-18T15:56:35.666777888Zのように表現される
    • オフセットを指定すると2011-11-18T18:56:35.666777888+03:00のように表現される
  • time_fmt_datetime()、time_fmt_date()、time_fmt_time()はそれぞれdatetime、date、time文字列を返す
    • タイムゾーンオフセットを任意で受け取れる
  • time_parse()はフォーマット済み文字列をTime値にパースする
    • ISO 8601のナノ秒とタイムゾーンを含む文字列
    • ISO 8601のナノ秒とUTC Z文字列
    • ISO 8601のタイムゾーンを含む文字列
    • ISO 8601 UTC文字列
    • YYYY-MM-DD HH:MM:SS形式のUTC日時
    • YYYY-MM-DD形式のUTC日付
    • HH:MM:SS形式のUTC時刻
  • time_parse()がサポートするレイアウトは限定された集合である

Duration定数

  • 共通のDurationをナノ秒で返す関数が提供される
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

実装基盤とインストール

  • 拡張はCで実装されているが、設計と実装はGo標準ライブラリのtimeパッケージに大きく基づいている
    • 同パッケージはBSD 3-Clause Licenseである
  • インストールはlatest releaseをダウンロードし、SQLite CLIで拡張をロードする方式である
    • 例：.load ./time
    • ロード後はselect time_now();のようなクエリを使用できる

関連記事

1件のコメント

 
GN⁺ 2024-08-16
Hacker Newsのコメント

  • Jon Skeetが有名にまとめていたタイムゾーン変更や現地時刻の不連続のような特殊ケースも処理するのか気になる
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphileも10分の動画でとても分かりやすく説明している
    https://www.youtube.com/watch?v=-5wpm-gesOY
    かなり前に、日付/時刻や暗号化ライブラリは自作しないと学んだ。致命的に刺さる境界ケースが無数にあるので、こういう新しいライブラリを見ると懐疑的になる

    • このライブラリは現地時刻の概念をまったく扱っていない。すべてUTCベースの時刻で、ユーザーがタイムゾーンオフセットを与えることはできるが、タイムゾーンオフセットを計算する難しい部分は呼び出し側が行う必要がある
      ドキュメントはもう少し明確でもよいと思う。作者は “time zones” と言っているが、ライブラリが実際に扱っているのはタイムゾーンオフセットだけだ。タイムゾーンは America/New_York のようなもので、タイムゾーンオフセットは UTC との差を指す。ニューヨークは今日は -14400 秒だが、夏時間の切り替えのため数か月後には -18000 秒になる

  • 3つの異なる時間表現/スケールが興味深い。たとえば数十億年の範囲でナノ秒精度が必要なユースケースが何なのか分からない
    さらにややこしいのは、時間の粒度は極端に細かいのに、duration ではナノ秒精度の範囲が ±290 年しかない点だ

    • いったんナノ秒精度を使うと決めたなら、64ビット表現では 584 年しか収まらず、それでは十分ではない。少なくともあと 2 ビットないと 2024 年を表現できない
      しかし 2 ビット足すなら、16 ビットや 32 ビット足さない理由もない。そうすれば 30cm を光が進む時間を計算する人から宇宙の年齢を計算する人まで全員をカバーできる
      設計判断はたぶんこういう流れだったのだろうと想像する :)
      もちろんうるう秒対応なしで秒未満の正確さを提供するのは難しいし、人類文明以前のうるう秒対応に何の意味があるのかも曖昧だ
    • この方式は私と何千人もの他の Go 開発者にとてもよく合っていた。だからこのアプローチを選んだ

  • 関連する脇道の話だが、データベースは単位を追跡すべきだ。時間カラムがあるなら、たとえば float64 秒単位の duration と宣言できるべきだ
    そうすれば SELECT * FROM my_table WHERE duration_s >= 2h のように書けて、データベースが自動的に “2h” を 7200.0 秒に変換し、テーブルスキャン中に同じ単位同士で比較してくれるはずだ
    数年前にこうしたネイティブな単位処理を持つ特化型 SQL データベースを作ったことがあるが、その前後で見たことがなく、UI エコシステムの穴のように思える
    時間に限る必要もない。質量、体積、情報量、温度など、単位の一覧全体を扱えるべきだ。SELECT 2h + 15kg -- type error! のような数学的に意味をなさない式もデータベースが拒否できる
    こうすれば分析ミスを早い段階で捕まえるのに大いに役立つ

  • 符号付き整数を使うかどうかを明示するのは重要だと思う。ドキュメントを読むと符号付きのようにも見えるが、そうでないようにも見える
    符号付き整数なら、同じ日付と時刻を表すビット列が複数生じうるので、それはよくない

    • 明らかに符号付きだ。“引くには負の duration を使え” と書かれている
      ただしビットパターンはライブラリ内部の問題だ。コードにバグを見つけられるなら当然指摘すべきだし、可能なら修正案も出せばよい
    • 「同じ日付と時刻を表すビット列が複数ある」とは、どういう意味で可能なのか?

  • SQLite3 に拡張可能な型システムがあれば本当によかったのに

    • PostgreSQL に少し貢献した立場から言うと: ダメ、これはやるべきではない!!!!
      拡張可能な型システムは、データベースのエンドユーザー性能にとって最悪だ。そうなるとクエリのパースや最適化で何一つ近道ができなくなる。すべてのオペランドの型を確認し、正しい演算子実装を探し、正しいインデックス演算子ファミリー/クラスを探すなど、クエリシステムカタログをひたすら参照し続ける必要がある
      値の入出力もシステムカタログに保存された関数を経由する。select 1 ですらシステムカタログを見ずには答えられない
      適切な組み込み型の集合と、構造体/JSON のような合成手段があるべきだ。PostgreSQL を除く大半のデータベースはこうなっていて、これが正しい方向だと強く思う

  • 少し怠惰な Ask HN 風の質問だが、経験上どちらのほうが有用または価値があるのか? ナノ秒表現か、それとも 1678〜2200 のようなナノ秒範囲を超える年の表現か?
    本格的な科学作業はしないので、ナノ秒の価値は非常に巧妙な実験や、もっと範囲が狭い金融取引の追跡くらいに限られるように見える
    一方で、歴史的な日付を表現できる能力のほうが、より頻繁に必要になりそうに思える。どう思う?

    • 歴史的な日付のほうが明らかに重要だ
      精度を10ナノ秒まで落とすだけでも、実務上十分な範囲が得られる
    • ハンマーとドライバーのどちらがより有用かと尋ねるようなものだ。作業次第だ

  • なぜ Go スタイルのようにUnix タイムスタンプをナノ秒単位の signed int64で使わないのか気になる。ナノ秒精度で何百万年もカバーはできないだろうが、本当にそれが必要なのか?

    • その精度とサイズでは1678年から2262年までしかカバーできないので、歴史的な日付と時刻を表現する能力が大きく制限される
    • Unix タイムスタンプをナノ秒で保存するのは Go スタイルではないが、この拡張ならそうすることはできる
      select time_to_nano(time_now());
      -- 1722979335431295000

  • “epoch 以後の秒” のような表現は、本当にその意味のときだけ使ってほしい
    select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); が何を返すのか気になる

    • なぜそう望むのか?
      もっともらしい理由はいくつか思い浮かぶが、本当に重要なのは「どのepochか?」だけだ。UNIX ベースのシステムやその動作を模倣しようとするシステムでは明確に定義されている。しかし不満の中身が語られていないので、なぜ今のようになっているのかを反論したり正当化したりしづらい
      time_date(1311, 11, 18) は、たいていのコンピューターシステムが使う epoch では定義されていないので、どんな結果でもありうる。MAX_INT、MIN_INT、0、もっともらしいが暦の改革を反映しない値、別の epoch に変換して正確な秒数を計算した値など何でもありうる。GMT/UTC より前はすべて現地時刻だったので、有効な epoch はないと主張することもできる
      もちろん負の値をサポートすべきかどうかは、どちらにも議論の余地がある。1970-1-1 0:00:00 UTC より正確に 24 時間前は -86400 だと期待できるが、“since” は強く正の値だけを示唆する
      他の人たちは別の理由でまったく異なる epoch を持つかもしれないし、使う領域の中で皆が合意しているならそれでも構わない
      それとも別の反対理由があったのか?
    • 「結果が Duration に保存できる最大値を超える場合、最大 duration が返される」とある