いよいよ年末ですね。分析担当の森藤です。

この記事は TVer アドベントカレンダー 24日目の記事です (遅くなりました)

みなさまははてな時間というものを聞いたことがありますでしょうか？

q.hatena.ne.jp

また、レポートなどの締め切りを確認するときに「今日までの締め切りって◯◯さんが業務を開始する明日の8時までですよね？」という確認をしたことはないでしょうか？

このような「日は変わってしまったが、前日として扱うことが便利な場合」に24時を飛び越え、26時や28時、と表現することが有ります。この表現のことを「30時間制」と言うらしいです。

ja.wikipedia.org

特に放送業界では24時を越えてからの深夜番組の表現を当日として扱うほうが都合がいいため頻繁に「25時23分から」という形で記述され、そして、この情報がそのまま私達 TVer には渡ってきます。 UTC に直してくれたら良いのに、と思うことはママありますが、そんな事を言っていても始まらないのでこのエントリで紹介する SQL をご紹介します。

ちなみに余談ですが Excel および Google Spreadsheet はデフォルトで対応しています。すごい！

本題

このように複雑な日付形式を扱う TVer ですが、情報提供のパターンが複数存在し(STRING なので・・・)、具体的には下記のいずれかおよび NULL および空文字のいずれかの可能性があります

20221208
2022-12-08
2022-12-08 22:34
2022-12-08 25:21
2022-12-08 22:34:00
2022-12-08 25:21:22
Sat, 28 May 2022 00:55:00 +0900
0000-00-00 00:00

これらをキチンとパースするか NULL にするためのクエリがこちらです

SELECT 
    -- 元の文字列
    onair_start_date,
    -- 最初に NOT NULL になった値を返す (ちなみに素でこのスペルは暗記できない)
    COALESCE(
        -- 一番、当たらないフォーマット (Timezone がはいっている)
        SAFE.PARSE_TIMESTAMP("%a, %d %b %Y %H:%M:%S %Z", onair_start_date),
        -- 秒がないケース
        SAFE.PARSE_TIMESTAMP("%Y-%m-%d %H:%M", onair_start_date, "Asia/Tokyo"),
        -- YYYYmmdd でくるケース。時間がないのは仕方がない
        SAFE.PARSE_TIMESTAMP("%Y%m%d", onair_start_date, "Asia/Tokyo"),
        -- 全部これで処理できるなら良かったのに
        SAFE.TIMESTAMP(onair_start_date, "Asia/Tokyo"),
        -- 30時間制！
        SAFE.TIMESTAMP(FORMAT("%t %02d:%s:%s",
            DATE_ADD(SAFE.PARSE_DATE("%Y-%m-%d", REGEXP_EXTRACT(onair_start_date, r"^(\d{4}-\d{2}-\d{2})")), INTERVAL 1 DAY),
            SAFE_CAST(REGEXP_EXTRACT(onair_start_date, r"^\d{4}-\d{2}-\d{2}\s(\d{2}):\d{2}") AS INT64) - 24,
            REGEXP_EXTRACT(onair_start_date, r"^\d{4}-\d{2}-\d{2}\s\d{2}:(\d{2})"),
            IFNULL(REGEXP_EXTRACT(onair_start_date, r"^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:(\d{2})"), "00")
        ), "Asia/Tokyo")
    ) AS oa_at,
FROM
    UNNEST([
        "20221208",
        "2022-12-08",
        "2022-12-08 22:34",
        "2022-12-08 25:21",
        "2022-12-08 22:34:00",
        "2022-12-08 25:21:22",
        "Sat, 28 May 2022 00:55:00 +0900",
        "0000-00-00 00:00"
    ]) AS onair_start_date

この結果がこうなります。

実行結果

斯くして、私達は UTC ですべての放送時間を扱えるようになり平和になりました。

もし Sat, 28 May 2022 28:55:00 +0900 という表現が来たら、地球はかいばくだんを取り出すドラえもんになってしまう気がしています。

最後に

TVerではいろいろとデータがあるのですが、全てが美しいわけではありません。この状態をなんとかできるデータにするのもまたデータ分析の面白さの一端ではあるかと思っています。 Google も「Google の使命は、世界中の情報を整理し、世界中の人々がアクセスできて使えるようにすることです」と言っていますので、崇高な仕事です。

もし「世界を整理する」ことに、興味があれば下記からご連絡いただければありがたいです。

tver.co.jp