【SQL】データ分析で必須！『日時』の操作知識まとめ

【SQL】データ分析で必須！『日時』の操作知識まとめ

データ分析において、『日時』データの取り扱いって、ちょっと面倒ですよね...

次の中で、どれか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"と省略できるので、覚えておくと便利ですよ！

日時操作はちょっと複雑なので、分からないことがあったら、またこの記事を参照いただけるとうれしいです！

ここまで読んでいただき、ありがとうございました！
また次の記事でお会いしましょう(^^)/

-SQL
-, , , , , , , ,