SQLiteで日付時刻を扱う際のポイント

#要点

SQLiteでは日付時刻は文字列として格納されるが、日付時刻型があると思って扱った方が楽。
SQLiteは日付時刻をUTCとして保持する。
SQLiteに日付時刻を渡す・取り出す際には関数を使用する。
SQLiteに日付時刻を渡す・取り出す際には時差により補正することができる。

　SQLiteでは日付・時刻・日付時刻は単なる文字列として扱われる。SQLiteで格納される日付時刻にはタイムゾーンの概念はなく、すべての日付時刻をUTCとして保持する¹。ただし、記録してある日付・時刻・日付時刻を取得する際に、欲しいフォーマットに変換したり時差を考慮したりすることができる。
　SQLiteでは日付等は文字列として扱われるが、便宜上「日付時刻型がある」と考えた方が都合がいい²。データベースには日付時刻型として格納し、取り出す際に文字列に変換する。

#背景
　さくらのレンタルサーバーライトでデータベースを使いたかったので、どうしてもSQLiteを使う必要があった。

##文字列<->日付時刻型の変換
　日付等を表す文字列を日付時刻型に変換したり、日付時刻型を文字列に変換するには、関数を利用する。
date(), time(), datetime() は文字列->日付時刻型への変換を行う。INSERT文、UPDATE文、WHERE句でよく使う。
strftime() は日付時刻型->文字列への変換を行う。SELECT文でよく使う。

SQL

SELECT date('2017-12-05'); --文字列から日付へ
SELECT datetime('2017-12-05'); --文字列から日付時刻へ
SELECT strftime('%Y-%m', date('2017-12-05')); --文字列から日付へ、それを文字列へ

2017-12-05
2017-12-05 00:00:00
2017-12

　ちなみに、strftime()は日付時刻を表す文字列->文字列への変換もできる。strftime()の書式は参考サイト1を参照。

SQL

SELECT strftime('%Y年%m月%d日', '2017-12-05');

2017年12月5日

　ちなみに、date(), time(), datetime()は日付時刻型->日付時刻型の変換もできる。

SQL

SELECT date(datetime('2017-12-05 12:34:56'));

2017-12-05

#現在時刻を取得する
　CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE は日付時刻型、'now'は文字列で、それぞれ現在の日付時刻・時刻・日付として使うことができる。ただしいずれもUTC。

現在 2017年12月5日 12時34分56秒 +09:00

SQL

SELECT CURRENT_TIMESTAMP;
SELECT date(CURRENT_TIMESTAMP);
SELECT strftime('%Y年%m月%d日', 'now');

2017-12-05 03:34:56
2017-12-05
2017年12月05日

##CURRENT_DATEの取り扱い(1)
　CURRENT_DATEがUTCの現在の日付であることに注意する。
　2017-12-05 07:00:00 +09:00 JST は 2017-12-04 22:00:00 +00:00 UTCであるから、日本時間2017年11月17日7時00分00秒に以下のSQL文を実行すると予想していない結果になるかもしれない。

現在 2017年12月5日 7時00分00秒 +09:00

SQL

SELECT strftime('%Y-%m-%d', CURRENT_DATE); --2017-12-05を予想

2017-12-04

　DEFAULT句でCURRENT_DATEを設定する際には特に注意する。

#時差を補正する
　date(), datetime(), time(), strftime()ではmodiferに時差を指定すると、時差を考慮して変換することができる。
'localtime' を指定すると現地時間に、'utc' を指定するとUTCに変換してくれる。
modifierに指定できる内容は参考サイト1を参照。

文法

date('日付文字列', modifier, modifier...)
datetime('日付時刻文字列' modifier, modifier...)
time('時刻文字列', modifier, modifier, ...)
strftime('日付時刻フォーマット %Y年 %m月 %d日 %H時 %M分 %S秒', datetime, modifier, modifier...)

現在 2017年12月5日 12時34分56秒 +09:00

SQL

SELECT datetime('now', '+9 hours'); ---UTCをJSTに
SELECT datetime('2017-12-05 12:34:56', 'utc'); ---JSTをUTCに
SELECT strftime('%m月%d日 %H時%M分', CURRENT_TIMESTAMP, 'localtime'); --UTCをJSTに 

2017-12-05 12:34:56
2017-12-05 03:34:56
12月05日 12時34分

　繰り返すが、'now', CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIMEはUTCであることを忘れずに。
なお、SQLiteはUTCで日付時刻等を保持するため¹、たとえばDEFAULT CURRENT_TIMESTAMPとする際には時差の考慮は不要。

##localtime 超便利…？

この節に限り現地時間 = 日本時間(UTC +09:00) とします。

現在 2017年12月5日 01時23分45秒 +09:00

SQL

SELECT datetime('now', 'localtime');
SELECT datetime('now', 'utc');
SELECT datetime('2017-12-05 01:23:45', 'localtime');
SELECT datetime('2017-12-05 01:23:45', 'utc');

2017-12-05 01:23:45
2017-12-04 16:23:45
2017-12-05 10:23:45
2017-12-04 16:23:45

まじか。
'localtime' は'now' やCURRENT_TIMESTAMP 等と組み合わせると現地時間にあわせてくれて、文字列やレコード内の値と組み合わせると'+9 hours'を表すっぽい。
'localtime' はSQLite側で現在時刻を挿入するときと、外部から時刻等を入れる場合とで挙動が変わる から気をつけよう。
'utc' は安定して'-9 hours' と同義。既にテーブル内にUTCとして格納した値にわざわざ'utc' を付けて取り出すと二重になってしまう。

##時差を補正するのがめんどくさい
　CURRENT_TIMESTAMP や'now' 等を使って現在時刻等をSQLite側で挿入しないならば現地時間での運用もありだと思う。DEFAULT 句で使うならばデータベース上は甘んじてUTCにしておく。

　以下の文は動かない。甘んじてUTCにしておく。

SyntaxError

CREATE TABLE Test(
id INTEGER PRIMARY KEY,
created_at TEXT DEFAULT datetime(CURRENT_TIMESTAMP, 'localtime')
);

#CURRENT_DATEの取り扱い(2)
　CURRENT_DATE とCURRENT_TIME とCURRENT_TIMESTAMP は実体が違う。
　CURRENT_DATE には本当に日付しか含まれない。CURRENT_DATE を使う際には、UTCで記録されることだけではなく時刻が切り捨てられることにも留意する。上述のようにDEFAULT 句では関数を指定できないのでこの場合時差補正もできない。DEFAULT CURRENT_DATE は諦めよう。

現在 2017年12月5日 12時34分56秒 +09:00

SQL

SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_DATE);
SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIME);
SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP);

2017-12-05 00:00:00
2000-01-01 03:34:56
2017-12-05 03:34:56

#date()で日付だけを切り出す
　date() は日付だけ、time() は時刻だけを切り出してくれる。このためGROUP BY句で分類したりORDER BY句で並べ替えたりする際に使うことができる。同時に時差補正もできていい感じ。

id [INTEGER]	created_at [TEXT]
1	2017-12-04 12:00:00
2	2017-12-05 12:00:00
3	2017-12-05 16:00:00
4	2017-12-06 16:00:00

SQL

.headers on
SELECT date(created_at) AS createdAt, count(*) AS count
FROM Test
GROUP BY createdAt; --時差補正なし

SELECT date(created_at, 'localtime') AS createdAt, count(*) AS count
FROM Test
GROUP BY creatdAt; --時差補正あり

createdAt|count
2017-12-04|1
2017-12-05|2
2017-12-06|1

createdAt|count
2017-12-04|1
2017-12-05|1
2017-12-06|1
2017-12-07|1

#modifierを使いこなす
　'modifier' は時差以外にも使える(というか本来の使い方はたぶんこっち)。

現在 2017年12月6日 12時34分56秒 +09:00

id [INTEGER]	created_at [TEXT]
1	2017-12-04 02:00:00
2	2017-12-05 02:00:00
3	2017-12-05 12:00:00
4	2017-12-06 12:00:00

SQL

---created_at が過去24時間以内であるレコードのidを取り出す
SELECT id
FROM Test
WHERE created_at > datetime('now', '-1 days');

3
4

#例文実行環境
SQLite 3.21.0 win32-x86版(コマンドラインツールがセットのやつ)
ON Windows10
　表示言語: 日本語
　タイムゾーン: (UTC +09:00) 大阪、札幌、東京

#参考サイト

1. [sqlite]日付と時間についての関数 | そんなヒロシの備忘録
2. SQLiteのタイムゾーン - それマグで！

1. 正確には「CURRENT_TIMESTAMPや'now'等がUTCである」といった方が正確かもしれませんが、ここではこのような考え方を使います ↩ ↩²

2. ここでは日付型・時刻型・日付時刻型を総称して「日付時刻型」と呼ぶことにします ↩