【SQL】PrestoのTIME関数の中で本当に使うものだけピックアップ!〜AthenaやBigQueryで活用〜
はじめに
お客様のサポートをする中で、「TIME系の関数が多くて使い分けが分かりにくい」という声をよく聞きます。後ほど説明しますが、TIMEの表記も「Data型」と「Unixtime型」の種類があり、それぞれの型に関数を使い分ける必要があるので、少しややこしさを感じるのではないでしょうか。
ただTIME関数を使うことで、すっきりしたSQLの式を書くことができます。
この記事では、そういったTIME関数について、私自身がよく使うものをまとめましたので紹介したいと思います。
参考:Prestoドキュメント
Timeについて前提確認
Timeの表記は2パターンあります。
この後の説明を理解して頂くためにまずはご紹介します。
Date型(YMD)
年/月/日/時/分/秒(yyyy/mm/dd/hh/mm/ss)の表記方法で、見た目で何年何月何日が分かる表記です。注意点としては、日本時間(JST)や協定世界時間(UTC)などの地域ごとに時間が異なってくるので、どの地域の時間か考慮する必要があります。
Unixtime型
コンピューターシステム上の時刻表現の1種で「1585666800」のような10桁の数値です。
1970/01/01を「0」として、1秒毎に1増える仕様になっており、Date型と違い地域ごとに時間を考慮する必要はありません。
Presto関数紹介
①:Unixtime型 → Date型の変換
DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s')
Unixtime型をDate型に変換することができ、時間以外にも「暦」や「曜日」も値として出すこともできます。
SELECT
DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS date
, DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%Y') AS year
, DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%Y-%m') AS month
, DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%Y-%m-%d') AS day
, DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%M') AS months
, DATE_FORMAT(FROM_UNIXTIME(1601317221, 'Asia/Tokyo'), '%W') AS day_of_week
date year month day months day_of_week 2020/9/29 3:20 2020 Sep-20 2020/9/29 September Tuesday
②:Date型 → Unixtime型の変換
TO_UNIXTIME(cast('yyyy-MM-dd' as TIMESTAMP))
Date型をUnixtime型に変換することができます。
SELECT
TO_UNIXTIME(cast('2020-08-22 03:04:05' as TIMESTAMP)) AS time
time 1598065445
③:時間の足し算/引き算
CAST('yyyy-MM-dd' AS TIMESTAMP) + interval '5' day
時間の足し算については、date_addなどの関数もありますが、私はintervalを使うことが多いです。
時間の粒度についても「時」「日」「月」「年」などがあり、足したり引いたりすることができます。
SELECT
cast('2020-10-27' as TIMESTAMP) AS target_day
, cast('2020-10-27' as TIMESTAMP) + interval '10' hour AS after_10_hour
, cast('2020-10-27' as TIMESTAMP) - interval '2' hour AS before_2_hour
, cast('2020-10-27' as TIMESTAMP) + interval '5' day AS after_5_day
, cast('2020-10-27' as TIMESTAMP) - interval '20' day AS before_20_day
, cast('2020-10-27' as TIMESTAMP) + interval '4' month AS after_4_month
, cast('2020-10-27' as TIMESTAMP) + interval '2' year AS after_2_year
④:時間を指定した時間に丸める
DATE_TRUNC('year', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')) AS year
, DATE_TRUNC('month', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')) AS month
, DATE_TRUNC('week', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')) AS week
, DATE_TRUNC('day', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')) AS day
unixtimeの時間を区切りの良い時間に丸めてくれます。①のDATE_FORMATと組み合わせて利用することが多いです。
SELECT
DATE_FORMAT(FROM_UNIXTIME(1601311123, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS date
, DATE_FORMAT(DATE_TRUNC('year', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')), '%Y-%m-%d %H:%i:%s') AS year
, DATE_FORMAT(DATE_TRUNC('month', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')), '%Y-%m-%d %H:%i:%s') AS month
, DATE_FORMAT(DATE_TRUNC('week', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')), '%Y-%m-%d %H:%i:%s') AS week
, DATE_FORMAT(DATE_TRUNC('day', FROM_UNIXTIME(1601311123, 'Asia/Tokyo')), '%Y-%m-%d %H:%i:%s') AS day
まとめ
私がよく使用するTime系の関数は以上になります。
それぞれ関数単独でご紹介しましたが、本来はこれらを組み合わせて集計を実施することが多いです。是非基礎を理解して頂き、バリバリ活用してください!
関連記事
Legolissに関するお問い合わせはこちらへ!