データ分析において、『日時』データの取り扱いって、ちょっと面倒ですよね...
次の中で、どれか1つでも当てはまる方は、この記事を読んで、一緒に日時に強くなりましょう！
- タイムゾーンの違い が分からない
- データ型の違い が分からない
- データ型の変換方法が分からない
- 日時差の算出方法 が分からない
- 日時のフォーマット方法が分からない
（検証環境：BigQuery）
目次
タイムゾーンについて
タイムゾーンとは
「今の時間は協定世界時（UTC）の何時間差か？」
を示したものになります。
タイムゾーンの書き方
タイムゾーンは「UTC+〇〇」で表され、〇〇に各国とUTCとの時差が入ります。
日本の標準時（JST）は「UTC+9」と書けますが、これは「UTCに9時間プラスした時間」という意味になりますね。
（ちなみにガーナやアイスランドは、「UTC+0」となり、UTCそのものが標準時となっています。）
GMTとUTC
小学校の社会の時間に、「グリニッジ天文台」という言葉を聞いたことがありませんか？
過去には、このグリニッジ天文台における時間（GMT）が世界の標準時として決めてられていたようです。
ただ、現在はより正確なセシウム原子時計（原子であるセシウムの動きから時間を読み取る時計）を元にしたUTCが、世界の標準時になっているようです。
この標準時にまつわる話は、歴史が深いため、気になる方はWikipediaなどを読んでみるとよいかもです！
日時のデータ型について
日時でよく使うデータ型は、次の3つです。
TIMESTAMP型
UTC+0 に変換された日時を示します。 TIMESTAMPの値は、日本の9時間前の日時が格納されているため、分析時はTIMESTAMPの日時を＋9時間して日本時間に変換することが多いです。
例）2022-1-3 10:40:58
DATETIME型
タイムゾーン関係なく、日時を示します。大抵、日本では日本時間が格納されています。
例) 2022-1-3 19:40:58
DATE型
タイムゾーン関係なく、日付を示します。DATETIMEの時刻がないVerです。
例) 2022-1-3
日時のデータ型変換について
データ型変換が必要な理由
以下のクエリの場合、SELECT句でエラーが発生します。
/*変数を定義して代入*/
DECLARE a TIMESTAMP;
DECLARE b DATETIME;
DECLARE x DATETIME;
SET a = "2022-2-3 13:00:00";
SET b = "2022-2-4 13:00:00";
SET x = "2022-2-5 13:00:00";
/*xとbはDATETIME型で、aのみTIMESTAMP型。*/
/*型が統一されていないため、エラーが発生する。*/
SELECT
(CASE WHEN x BETWEEN a AND b THEN 1 ELSE 0 END) これはBETWEEN構文において、データ型の異なるTIMESTAMPとDATETIMEを同列に扱うことができないためです。このようなケースでは、データ型を変換し、それぞれのデータ型を統一する必要があります。
データ型の変換方法
データ型の変換方法は以下の通りで、とても簡単です！
TIMEATAMP型への変換 → TIMESTAMP(TIMESTAMP型へ変換したい値)
DATETIME型への変換 → DATETIME(DATETIME型へ変換したい値)
DATE型への変換 → DATE(DATE型へ変換したい値)
例えば、先ほどのクエリを以下のように修正することで、データ型が統一されてエラーが回避できます。
/*変数を定義して代入*/
DECLARE a TIMESTAMP;
DECLARE b DATETIME;
DECLARE x DATETIME;
SET a = "2022-2-3 13:00:00";
SET b = "2022-2-4 13:00:00";
SET x = "2022-2-5 13:00:00";
/*xとbはDATETIME型で、aのみTIMESTAMP型。*/
/*aのみDATETIME型へ変換することで、データ型がDATETIME型に統一され、エラーが回避できる*/
SELECT
(CASE WHEN x BETWEEN DATETIME(a) AND b THEN 1 ELSE 0 END) 日時操作について
ここでは、データ分析時に利用する主な日時操作をまとめます！
日時の加算
/*TIMESTAMP型の日時を＋9時間したい場合*/
TIMESTAMP_ADD(元の値, INTERVAL 9 HOUR)
/*DATETIME型の日時を＋10日したい場合*/
DATETIME_ADD(元の値, INTERVAL 10 DAY)
/*DATE型の日付を＋1ヶ月したい場合*/
DATE_ADD(元の値, INTERVAL 1 MONTH)日時の減算
/*TIMESTAMP型の日時を－9時間したい場合*/
TIMESTAMP_SUB(元の値, INTERVAL 9 HOUR)
/*DATETIME型の日時を－10日したい場合*/
DATETIME_SUB(元の値, INTERVAL 10 DAY)
/*DATE型の日付を－1ヶ月したい場合*/
DATE_SUB(元の値, INTERVAL 1 MONTH)日時差の算出
/*TIMESTAMP同士の日時差*/
TIMESTAMP_DIFF(元の値1, 元の値2, DAY)
/*DATETIME同士の日時差*/
DATETIME_DIFF(元の値1, 元の値2, DAY)
/*DATE同士の日時差*/
DATE_DIFF(元の値1, 元の値2, DAY)↑ それぞれ、3つ目の引数を以下にすることで、算出結果が変わります。
YEAR → 年単位で差を算出
MONTH → 月単位で差を算出
DAY → 日単位で差を算出
HOUR → 時間単位で差を算出
日時のフォーマットについて
最後に、データ分析時によく使う、日時のフォーマット方法についてまとめます！
よく使う日時フォーマット
/*TIMESTAMP型の"2022-11-12 23:59:59" を "2022-11-12" にフォーマットする場合*/
FORMAT_TIMESTAMP("%Y-%m-%d", "2022-11-12 23:59:59")
/*DATETIME型の"2022-11-12 23:59:59* を "2022年11月12日23時59分59秒" にフォーマットする場合*/
FORMAT_DATETIME("%Y年%m月%d日%H時%M分%S秒", "2022-11-12 23:59:59")
/*DATE型の"2022-11-12* を "2022.11" にフォーマットする場合*/
FORMAT_DATE("%Y.%m", "2022-11-12")
↑ ちなみに、"%Y-%m-%d" は "%F"と省略できるので、覚えておくと便利ですよ！
日時操作はちょっと複雑なので、分からないことがあったら、またこの記事を参照いただけるとうれしいです！
ここまで読んでいただき、ありがとうございました！
また次の記事でお会いしましょう(^^)/
