Knowledge & Column

AWSSQL

【SQL】PrestoのTIME関数の中で本当に使うものだけピックアップ！〜AthenaやBigQueryで活用〜

■はじめに

お客様のサポートをする中で、「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系の関数は以上になります。
それぞれ関数単独でご紹介しましたが、本来はこれらを組み合わせて集計を実施することが多いです。是非基礎を理解して頂き、バリバリ活用してください！

〔関連記事〕

【SQL】TreasureData独自関数が覚えられない！TD利用歴2年半のエンジニアがよく使うものだけをまとめてみた！【WEBログ分析の関数編】

【Athena】「簡単！安価！」にSQLで分析できる環境を、AWS-Athenaを利用して構築する！概要から実際の手順をまとめてみた！

【SQL】TreasureData独自関数が覚えられない！TD利用歴2年半のエンジニアがよく使うものだけをまとめてみた！【Time系の関数編】

, , , , , , ,

関連記事