【BigQuery】#1 Time Stampデータを日付形式に変換する【組み合わせ分析】

TIME STAMPをYYYY-MMのフォーマットに変換する

データ内にある「time_stamp」列をYYYY-MMの表示形式に変換します。
SQLのコードは下記です。

FORMAT_DATE("%Y-%m", DATE(TIMESTAMP_SECONDS(time_stamp)))

FORMAT_DATE関数で指定したフォーマットにデータを変換します。
ここでは"%Y-%m"のフォーマットを指定し「YYYY-MM」で表記されるようにしています。
"%Y-%m"の部分を変更することにより、日付の表記を変更することができます。

日付変換フォーマット例

• "%Y" = 4桁の年(2022)
  
• "%y" = 2桁の年(22)
  
• "%M" = 英語の月名(JULY)
  
• "%m" = 2桁の月(07)
  
• "%d" = 2桁の日付(01)
  
• "%k" = 24時間表記の時間(24)
  
• "%i" = 2桁の分(01)
  
• "%s" = 2桁の秒(01)
  
  今回使用するデータにある「time_stamp」は秒までのタイムスタンプのため、関数はTIMESTAMP_SECONDSを使います。
  もしミリ秒まで情報が含まれる場合は、TIMESTAMP_MILLISを使います。
  
  

  BigQueryでのクエリ結果

  それではBigQuery上で実際にtime_stamp列をYYYY-MM表記に変換します。
  

  クエリ

SELECT  
  time_stamp,
  FORMAT_DATE("%Y-%m", DATE(TIMESTAMP_SECONDS(time_stamp))) AS YYYYMM,
  FORMAT_DATE("%Y-%m-%d", DATE(TIMESTAMP_SECONDS(time_stamp))) AS YYYYMMDD
FROM 
  `table_name`
GROUP BY 
  time_stamp
ORDER BY 
  time_stamp ASC

今回使用するフォーマットはYYYY-MMのみですが、日付フォーマットの変換の違いをみるためにYYYY-MM-DDバージョンもクエリに含みました。

クエリ実行結果

time_stampをYYYY-MMやYYYY-MM-DD形式に変換することができました。

次回からはID毎にfacilityの組み合わせを作成していきます。