【BigQuery】STRING型日時データをDATETIME型に変換する方法

クラウドプラットフォーム
2023.07.13

STRING型（文字列）の年月日をDATETIME型（日時）に変換しようと、DATETIME() を使っても変換できません。変換するにはDATETIME型に適した表記に変えなければならないからです。

本日はSTRING型からDATETIME型に変換する方法をご紹介します。

DATETIME型は「YYYY-MM-DD」と表す必要がある

DATETIME型で格納するには年月日の間を – で繋ぐ必要があります。
さきほどは – がなかったからエラーだったんですね。（さすがに自動で変換してくれない）

DATETIME 型は、日付と時間の両方の部分を含む値に使用されます。 MySQL は、DATETIME 値を'YYYY-MM-DD hh:mm:ss'形式で取得して表示します。 サポートしている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

11.2.2 DATE、DATETIME、および TIMESTAMP 型

MySQLの公式リファレンスを見ても、DATETIME型の値は「YYYY-MM-DD」で表示するとわかります。冗長的なコードですが、SUBSTR()関数を使って文字列に変更を加えましょう！

SELECT
  DATETIME(CONCAT(SUBSTR(`発売日`, 1, 4), '-', SUBSTR(`発売日`, 5, 2), '-', SUBSTR(`発売日`, 7, 2))) AS datetime
FROM
  `sql-book-384011.sample.20230712_pokemon_index_1-3`
WHERE
  `図鑑番号` = 1

年月日の間に – を入れるだけでSTRING型からDATETIME型に型変換ができました！

「年月日なのに DATETIME型に直せない。。。」
そんなときはデータの中身を確認してみましょう。もしかしたら -（ハイフン）が足りないかもしれません。

もっと簡単に変換するなら parse_datetime(‘%Y%m%d’, カラム名)を使おう！

SUBSTR() を使うとコードが読みにくくなるので使いたくない。
もっと簡単に変換するなら parse_datetime(‘%Y%m%d’, カラム名) がおすすめ！

SELECT
  parse_datetime('%Y%m%d', `発売日`) AS datetime
FROM
  `sql-book-384011.sample.20230712_pokemon_index_1-3`
WHERE
  `図鑑番号` = 1

parse_datetime()はBigQueryで用意されている関数で、STRING型をDATETIME型に変換する役割があります。

こんな便利な関数があるんだったら最初から紹介しとけばよかった。（後で気づきましたが、- が必要なことも伝えたかったのでそのままで）

名前：PARSE_DATETIME
要約：STRING型の値をDATETIME型の値に変換する

Datetime 関数

まとめ

STRING型（文字列）の年月日をDATETIME型（日時）に変換するには年月日の間が – （ハイフン）で繋がれている必要がありました。

型変換の方法は主に2つ

  1. SUBSTR()を使って文字列を-（ハイフン）繋ぎにしてから、DATETIME()を使う
  2. parse_datetime(‘%Y%m%d’, カラム名)を使う

これで型変換もバッチリですね！