QuickSight の parseDate 関数を使って、数値フィールドを日付形式に変換する

いわさです。

先日、数値列にyyyyMMdd形式で日付を示すデータが格納されているデータソースをQuickSight上で日付形式で扱う機会がありました。
その際に、数値から日付への計算を行ったのですがサポートされているフォーマットが決まっていることなどを知りましたので共有です。

前提として、数値フィールドにyyyyMMdd形式で年月日が格納されているケースです。
データソースのシステムに依る部分ですので元の形式の是非について触れる必要はありません。

本日（2022年7月14日）であれば20220714という8桁の数字が格納されているものとします。
方法として、数値から日付へ直接変換出来ないので、数値フィールドを文字列へ変換(toString関数)し、日付のフォーマットを指定して日付型に変換(parseDate関数)を行いました。

数値から文字列に変換すると同じフォーマットでも parseDate に失敗する

まずは、本日時点で以下のような挙動になっています。

まず、以下のように文字列のyyyyMMddを直接parseDateする場合は、変換出来ます。

parseDate("20220713", 'yyyyMMdd')

そして、ここからが謎だったのですが、toStringで数値から文字列に変換してからparseDateで文字列から日付に変換する場合、以下のように変換が出来ませんでした。

parseDate(toString(20220713), 'yyyyMMdd')

そうなの？

関数式にサポートされていない日付が含まれています。日付形式を修正して、もう一度試してください。
UNRECOGNIZED_FORMAT

ちなみに以下のように、toString自体は期待した結果となっています。

toString(20220713)

parseDate はサポートされている日付形式が決まっている

実は、parseDate のリファレンスによると、サポートされている日付形式が以下のように決まっています。

サポートされている日付形式でやってみる

では、サポートされている日付形式に記載されているフォーマットで試してみます。
まずはyyyy-MM-ddです。

parseDate(concat(substring(
              toString(20220713), 1, 4),
              '-', substring(toString(20220713), 5, 2),
              '-', substring(toString(20220713), 7, 2)),
          'yyyy-MM-dd')

今度は変換に成功しました！

toStringやsubstringを何度も行っているのと、QuickSightではUTCで動作するのでオフセットを設定したい場合があるので、今回の日付フォーマットを延長する形で以下のように時刻も指定した形で文字列結合して日付へ変換するようにしてみました。

parseDate(concat(toString(20220713), 'T000000'), "yyyyMMdd'T'HHmmss")

こちらも正しく変換されました。

さいごに

本日は、QuickSight の parseDate 関数を使って、数値フィールドを日付形式に変換す際の注意点を紹介しました。

リファレンスを見ると書いてあるとおりではあるのですが、以下が期待どおり変換されてしまったので混乱してしまいました。

parseDate("20220713", 'yyyyMMdd')

アプリケーションだとよく指定するフォーマットだと思うので、それもあってサポートされていないフォーマットだということに気がつくのが遅れました。
エラーメッセージを載せておいたので、同じような問題で困っている方のヒントになれば良いと思っています。