いつから日付が0時で変わると錯覚していた…？なときの BigQuery 的対応

いつハッピーニューイヤーするか

今年も無事終わりますね。

ということは、もうすぐハッピーニューイヤーしなければなりません。

そこでふと思ったのですが、 日付っていつ変わるんでしょうね？

意味不明なことを言っている感じがしますが、深夜1時に就寝するとき、もう「次の日」になっていると思って寝ている人は少ないのではないでしょうか。

機械的には 0時を境界線として作業するわけですけど、やっぱりそれだとデータ分析等の場面でも微妙だったりします。

たとえば・・・

特に放送の場合のあるあるですが、 「深夜番組」は機械的な日付ではなく深夜2時=前日の26時的な扱いにしたい
ECをはじめとするWEBサイトについても、 深夜3時くらいまでは深夜テンションで閲覧している方がいるわけなので、日付を跨いで処理しないほうが示唆があったりする

みたいなことは、どんな場面でもあり得るのではないでしょうか。

今回はBigQueryでそのあたりの処理をどうすべきか考えます。

まずは、何も考えずやろうとするとこうなるよねという話です。

放送の場合はだいたい朝5時くらいに日付変更線を設定することが多いのですが、

SELECT DATE(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 HOUR), 'Asia/Tokyo')

としてあげれば、5時間引いた状態で日付を算出できます。

この方法の良いところは、意図を把握しやすいことですが、

が少し微妙なポイントです。ただ、特に気にならないなら正直これでもいいような気もします。

そこでもう少しクエリをシンプルにしたいとしたときにどうすればいいかを考えてみます。

各地のタイムゾーンを活用してみたらどうなるでしょうか。

同様に朝5時に日付が変わるようにしようと思うと

ドバイが +04:00 つまり日本時間 +09:00 の5時間後に日付が変わる = 日本時間5時に日付が変わるので

DATE(CURRENT_TIMESTAMP(), 'Asia/Dubai')

としてあげると、朝5時を日付変更線とした処理が可能です。

最大のデメリットは、主要なタイムゾーンを頭に入れておく必要があることです。

しかし、手練れのエンジニアの皆さんにおかれましては 「タイムゾーンなど-12:00から+14:00まで全部地名一個ずつは列挙できるわ」 といったところかと思いますので問題なさそうです。私には無理です。

とはいえよく使うものは絞られると思いますので、意外といけるかも？

さらにいうと、以下のよう感じでタイムゾーンを定義しておけばまだ耐えられるかもしれません。

DECLARE DATE_5AM_TIMEZONE DEFAULT 'Asia/Dubai';
SELECT DATE(CURRENT_TIMESTAMP(), DATE_5AM_TIMEZONE)

細かい可読性とかの話を抜きにしたら、 ドバイ時間で日付コントロールしよか、 みたいな会話はちょっと面白い気もします。

ここまで引っ張っておいて元も子もない話ですが、実は先ほどまでの表現について、タイムゾーンについては地名ではなく +04:00 のような表記で入れることができます。

DATE(CURRENT_TIMESTAMP(), '+04:00')

最もシンプルで、個人的には基本的にこれを採用しています。

朝3時を日付変更線としたい場合は、日本時間の +09:00 から3時間分引いてあげて +06:00 とすればよいということです。

唯一ややこしいのは、 酔っ払ってるレベルで頭が回っていないときに +04:00 みたいな表記を見ると「日本時間4時に日付変更」と思ってしまいかねないところですね。そんなときはさっさと休めよという感じではありますが。

日本時間 +09:00 との関係性を頭の中で整理してあげる必要があります。

日本時間が +00:00 でないことが悔やまれますよね。

今回はタイムゾーンによる日付変更線の管理の話でした。汎用性のある内容かと思います。

しかし…タイムゾーンという概念はデータ分析のみでなく様々な場面ででてきますよね。ああ、タイムゾーン設定が漏れていたから日本時間じゃなくてUTCベースの日時で色々生成してしまった…なんてことは日常茶飯事なのではないでしょうか。

ハッピーニューイヤーな話に戻すと、私個人の話としては、大晦日も昔は0時まで割と余裕で起きていましたが、最近は23時くらいには限界を迎えるようになってきたので、個人的には +10:00 くらいで日付変わってほしいです。。

それでは皆様、良いお年をお迎えください 🎍