SQLite3で日付時刻文字列からミリ秒付きのUnixTimeを作る

準備

以下の要領で確認用のデータを作る

$ sqlite3 temp.db "CREATE TABLE 'Example'(Time TEXT)"
$ sqlite3 temp.db "INSERT INTO Example VALUES ('2024-06-18T21:19:30.833627900+09:00')"
$ sqlite3 --table temp.db "SELECT Time FROM Example"
+-------------------------------------+
|                Time                 |
+-------------------------------------+
| 2024-06-18T21:19:30.833627900+09:00 |
+-------------------------------------+

%fを使う方法

AIに聞いて答えてくれた方法

$ sqlite3 --table temp.db "SELECT strftime('%s', Time) || substr(strftime('%f', Time), 3) FROM Example"
+---------------------------------------------------------+
| strftime('%s', Time) || substr(strftime('%f', Time), 3) |
+---------------------------------------------------------+
| 1718713170.834                                          |
+---------------------------------------------------------+

subsec修飾子を使う方法

もう少しきれいな方法はないかなー、と思いながらSQLiteのソースコードを眺めて見つけた方法
subsecondでも可

$ sqlite3 --table temp.db "SELECT strftime('%s', Time, 'subsec') FROM Example"
+--------------------------------+
| strftime('%s', Time, 'subsec') |
+--------------------------------+
| 1718713170.834                 |
+--------------------------------+

メモ

いずれの方法でも小数点以下3桁より小さい値は表示できないようなので¹²、1ミリ秒より小さい時間を扱うには元の文字列のまま取り出して別の方法で処理する必要がありそう。
ただし「The current implemention increases the resolution from seconds to milliseconds, but this might increase to a higher resolution in future releases of SQLite」とあるので、将来的には変わるかもしれない。

1. https://github.com/sqlite/sqlite/blob/bebe2d8be8acfd02592c4972f4ba32c3b4e4a33f/src/date.c#L1430-L1435 ↩

2. https://github.com/sqlite/sqlite/blob/bebe2d8be8acfd02592c4972f4ba32c3b4e4a33f/src/date.c#L1499-L1500 ↩