athena(presto)で、dateとtimeからJSTを得る

<p>CloudFrontのログファイルをathenaでテーブルにすると、dateカラムとtimeカラムで別カラムになっている</p>
<ul>
<li><a href="https://docs.aws.amazon.com/ja_jp/athena/latest/ug/cloudfront-logs.html">Amazon CloudFront ログのクエリ - Amazon Athena</a></li>
</ul>
<p>この2つのカラムからdatetime (JST) のカラムを作りたい</p>
<h3>こんな感じでできる</h3>
<p>この辺を組み合わせる</p>
<ul>
<li><code>date_format</code></li>
<li><code>from_iso8601_timestamp</code></li>
<li><code>AT TIME ZONE 'Asia/Tokyo'</code></li>
</ul>
<p>文字列結合でiso8601フォーマットを作っている</p>
<ul>
<li>eg: <code>2019-08-21T18:04:04Z</code></li>
</ul>
<pre><code class="hljs language-sql"><span class="hljs-keyword">WITH</span> t <span class="hljs-keyword">AS</span> (
  <span class="hljs-keyword">SELECT</span>
    cdn_access_log.<span class="hljs-operator">*</span>,
    from_iso8601_timestamp(date_format(<span class="hljs-type">date</span>, <span class="hljs-string">&#x27;%Y-%m-%dT&#x27;</span>) <span class="hljs-operator">||</span> <span class="hljs-type">time</span> <span class="hljs-operator">||</span> <span class="hljs-string">&#x27;Z&#x27;</span>) <span class="hljs-keyword">AT</span> <span class="hljs-type">TIME</span> ZONE <span class="hljs-string">&#x27;Asia/Tokyo&#x27;</span> jst_datetime
  <span class="hljs-keyword">FROM</span>
    myproj.cdn_access_log
)
<span class="hljs-keyword">SELECT</span>
  <span class="hljs-type">date</span>,
  <span class="hljs-type">time</span>,
  jst_datetime
<span class="hljs-keyword">FROM</span>
  t
LIMIT
  <span class="hljs-number">3</span>
</code></pre>
<table>
<thead>
<tr>
<th>date</th>
<th>time</th>
<th>jst_datetime</th>
</tr>
</thead>
<tbody>
<tr>
<td>2019-08-21</td>
<td>9:04:04</td>
<td>2019-08-21 18:04:04.000 Asia/Tokyo</td>
</tr>
<tr>
<td>2019-08-21</td>
<td>9:04:01</td>
<td>2019-08-21 18:04:01.000 Asia/Tokyo</td>
</tr>
<tr>
<td>2019-08-21</td>
<td>9:04:05</td>
<td>2019-08-21 18:04:05.000 Asia/Tokyo</td>
</tr>
</tbody>
</table>
<h3>参考</h3>
<ul>
<li><a href="https://docs.aws.amazon.com/ja_jp/athena/latest/ug/cloudfront-logs.html">Amazon CloudFront ログのクエリ - Amazon Athena</a></li>
<li><a href="https://prestodb.github.io/docs/current/functions/datetime.html">6.13. Date and Time Functions and Operators — Presto 0.224 Documentation</a></li>
</ul>


date	time	jst_datetime
2019-08-21	9:04:04	2019-08-21 18:04:04.000 Asia/Tokyo
2019-08-21	9:04:01	2019-08-21 18:04:01.000 Asia/Tokyo
2019-08-21	9:04:05	2019-08-21 18:04:05.000 Asia/Tokyo