Home » sqlite » [sqlite] SQLiteでの日付型の扱い その1

[sqlite] SQLiteでの日付型の扱い その1

2017年8月14日月曜日

sqlite

日付型がない

SQLiteは型として日付(Date)型を持っていない。
なので、どうするかというと以下の３つのパターンがある。
  • ISO-8601形式 : TEXT
    ISO-8601の文字列形式で日時を保持する。
  • ユリウス通日 : REAL
    紀元前4713年1月1日の正午からの日数を実数型で保持する。整数部分が日数で小数部分が時刻を表す。
  • Unix時間 : INTEGER
    19070年1月1日真夜中(午前0時0分0秒)からの秒数を整数型で保持する。

Datatypes In SQLite Version 3 2.2. Date and Time Datatype
文字列形式は人間がテーブルの中身をパッと見てわかるというメリットがあるけど、異なるフォーマットの値が入り込んでしまうという危険性もある。
数値形式は人間が見て、すぐにはわからないけど、経過時間の差し引きが容易というメリットがある。

現在日時の表示

現在日時をそれぞれの方法で表示させるためには以下のようにする。
sqlite> select datetime('now');
2017-07-09 00:08:13
sqlite> select datetime('now', 'localtime');
2017-07-09 09:08:14
sqlite> select julianday('now', 'localtime');
2457943.88120931
sqlite> select strftime('%s', 'now', 'localtime');
1499591483

※ 最初の例のように'localtime'をつけないと、UTCになってしまうので、注意しよう。

表示形式変換方法

SQLiteはこれら３つの各形式間の変換関数を用意している。
各形式間の変換は datetime, julianday, strftime 関数で行う。
※ datetime, juliandayはstrftimeのラッパーで実際にはstrftimeさえあれば、すべてできる。
sqlite> --ユリウス通日からISO-8601
sqlite> select datetime(2457943.88120931);
2017-07-09 09:08:56
sqlite> --Unix時間からISO-8601
sqlite> select datetime(1499591336, 'unixepoch');
2017-07-09 09:08:56
sqlite> --Unix時間からユリウス通日
sqlite> select julianday(1499591336, 'unixepoch');
2457943.8812037
sqlite> --ISO-8601からユリウス通日
sqlite> select julianday('2017-07-09 09:08:56');
2457943.8812037
sqlite> --ISO-8601からUnix時間
sqlite> select strftime('%s', '2017-07-09 09:08:56');
1499591336
sqlite> --ユリウス通日からUnix時間
sqlite> select strftime('%s', 2457943.88120931);
1499591336

簡単にいうと、インプットとして文字列を渡すと、ISO-8601形式として解釈される。
数値を渡すとユリウス通日として解釈される。
数値をUnix時間として解釈させたい場合はオプション引数に'unixepoch'を渡せばいい。
Unix時間はdatetime, juliandayのようなラッパー関数がないため、strftimeに'%s'を適用して求める。

SQL As Understood By SQLite Date And Time Functions
 によると
select (julianday('now') - 2440587.5) * 86400.0;
という例があって、ユリウス通日とUnix時間の原理を知るにはいいけど、実際には関数を通して変換できる。
時刻:
ラベル: