こんにちは。
Legoliss データアナリストの音嶋健斗です。
このブログでは、データマーケティングにまつわる様々な情報をお届けします。
■はじめに
お客様のサポートをする中で、「TIME系の関数が多くて使い分けが分かりにくい」という声をよく聞きます。後ほど説明しますが、TIMEの表記も「Data型」と「Unixtime型」の種類があり、それぞれの型に関数を使い分ける必要があるので、少しややこしさを感じるのではないでしょうか。
ただTIME関数を使うことで、すっきりしたSQLの式を書くことができます。
この記事では、そういったTIME関数について、私自身がよく使うものをまとめましたので紹介したいと思います。
参考:Prestoドキュメント
■本記事の内容
・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
target_day | after_10_hour | before_2_hour | after_5_day | before_20_day | after_4_month | after_2_year |
2020-10-27 00:00:00 | 2020-10-27 10:00:00 | 2020-10-26 22:00:00 | 2020-11-01 00:00:00 | 2020-10-07 00:00:00 | 2021-02-27 00:00:00 | 2022-10-27 00:00:00 |
【④:時間を指定した時間に丸める】
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
date | year | month | week | day |
2020-09-29 01:38:43 | 2020-01-01 00:00:00 | 2020-09-01 00:00:00 | 2020-09-28 00:00:00 | 2020-09-29 00:00:00 |
■まとめ
私がよく使用するTime系の関数は以上になります。
それぞれ関数単独でご紹介しましたが、本来はこれらを組み合わせて集計を実施することが多いです。是非基礎を理解して頂き、バリバリ活用してください!
<このブログの執筆者>
株式会社Legoliss
データアナリスト 音嶋健斗