sqlite3 の datetime 関数について
これまでは日付間の計算をするときに便利な epoch で保存してたのだけど、database の中身を覗くときに不便に感じることが増えてきたので、これからは "%Y-%m-%d %H:%M:%S" 形式で保存するようにしたい。
sqlite3 にはそれなりの datetime 関数もあることに気づいたので調べてみた。
実験
実行環境は shell 版 sqlite3 にて。
sqlite3 hoge.db "select strftime('%s', '1970-01-01 00:00:00')"
=> 0
sqlite3 hoge.db "select strftime('%s', '1970-01-01 00:00:00', 'localtime')"
=> 32400
sqlite3 hoge.db "select strftime('%s', '1970-01-01 00:00:00', 'utc')"
=> -32400
timestring を表記するための TimeZone を modifier で指定できるのかと思っていたがどうやら違うらしい。
よくみると modifier は複数回、何回でも指定できる。
sqlite3 hoge.db "select strftime('%s', '1970-01-01 00:00:00', 'localtime', 'localtime')"
=> 64800
sqlite3 hoge.db "select strftime('%s', '1970-01-01 00:00:00', 'localtime', 'localtime', 'localtime')"
=> 97200
http://www.sqlite.org/lang_datefunc.html
The time string can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important.
拙訳:
time string は後続する 0 個以上の modifier(修飾子) により修飾され、日付/時刻を置き換える。 それぞれの修飾子はそれより左側の時刻の値に対して適用される。 修飾子は左から右に適用される。この順番は重要である。
どうやら単純に modifier(s) で指定した時差を読み込んだ timestring にどんどん足していく(あるいは引いていく)感じに思っておくとよいらしい。
現在の TZ での時刻表記を epoch にする
sqlite3 hoge.db "select strftime('%s', '1970-01-01 09:00:00', 'utc')"
=> 0
TZ を変更してみる。
TZ=UTC sqlite3 hoge.db "select strftime('%s', '1970-01-01 09:00:00', 'utc')"
=> 32400
日時などを加算できる。
sqlite3 hoge.db "SELECT strftime('%Y-%m-%d %H:%M:%S', '2000-01-01 00:00:00', '10 days')"
=> 2000-01-11 00:00:00
月初なども取得できる。
sqlite3 hoge.db "SELECT strftime('%Y-%m-%d %H:%M:%S', '2000-01-31 00:00:00', 'start of month')"
=> 2000-01-01 00:00:00
月末を取得する方法がない。。けど date 型などと言ってみても実体は文字列型なので
sqlite3 hoge.db "SELECT strftime('%Y-%m-99 %H:%M:%S', '2000-01-31 00:00:00')"
みたいにしとけば期間を取得するときには充分かな？
それを言えば月初の取得(ry
unixepoch 修飾子のこと
The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined.
拙訳:
"unixepoch" 修飾子は DDDDDDDDDD format 直後の後続として指定されたときのみ機能する。 この修飾子は通常のように Julian 日付としてはその値を解釈せずに、Unix Time (1970 年からの秒数) として解釈する。 もし "unixepoch" 修飾子が 1970 年からの秒数を表す DDDDDDDDDD 形式に後続しない場合、あるいは "unixepoch" 以外の修飾子を間に挟む場合、その挙動は未定義となる。
sqlite3 hoge.db "SELECT julianday('1970-01-01 00:00:00')"
=> 2440587.5
sqlite3 hoge.db "SELECT strftime('%J', 0, 'unixepoch')"
=> 2440587.500000001
つまり 2440587.5 は 1970-01-01 00:00:00 を表すユリウス日。
# ユリウス日って日本語圏だとまず使わないけど、どういう場面だと使うのだろう。
sqlite3 hoge.db "SELECT strftime('%Y-%m-%d %H:%M:%S', 1, 'unixepoch')"
=> 1970-01-01 00:00:01
まとめ
unix epoch から yyyy-MM-dd (UTC) などへ
sqlite3 hoge.db "SELECT datetime(311871600, 'unixepoch')"
=> 1979-11-19 15:00:00
TZ=UTC sqlite3 hoge.db "SELECT datetime(311871600, 'unixepoch')"
=> 1979-11-19 15:00:00
TZ=UTC sqlite3 hoge.db "SELECT datetime(311871600, 'unixepoch', 'localtime')"
=> 1979-11-19 15:00:00
unix epoch から yyyy-MM-dd (現地時刻) などへ
sqlite3 hoge.db "SELECT datetime(311871600, 'unixepoch', 'localtime')"
=> 1979-11-20 00:00:00
yyyy-MM-dd (現地時刻) など -> unix epoch
sqlite3 hoge.db "SELECT strftime('%s', '1979-11-20 00:00:00', 'utc')"
=> 311871600
yyyy-MM-dd (utc) など -> unix epoch
sqlite3 hoge.db "SELECT strftime('%s', '1979-11-19 15:00:00')"
=> 311871600
yyyy-MM-dd (現地時刻) など -> yyyy-MM-dd (utc)
sqlite3 hoge.db "SELECT strftime('%Y-%m-%d %H:%M:%S', '1979-11-20 00:00:00', 'utc')"
=> 1979-11-19 15:00:00
