【小ネタ】Amazon QuickSight でデータのタイムゾーンを変更できないか試してみた

#Amazon QuickSight

市田善久

2022.06.27

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

以前の記事で設備機器の稼働データを Amazon QuickSight で可視化していますが、時刻についてはタイムゾーンを UTC のまま QuickSight に渡していました。

今回は、QuickSight の表示で時刻データのタイムゾーンを JST にできないか考えてみました。

Amazon QuickSight は UTC

いきなり結論ぽい内容になりますが、QuickSight ではクエリ、フィルタ、表示のいずれも UTC が採用されるので、仮に JST でデータを送ったとしても UTC として処理されます。（ JST の時刻でも UTC に変換される）

QuickSight uses UTC time for querying, filtering, and displaying date data. When date data doesn't specify a time zone, QuickSight assumes UTC values. When date data does specify a time zone, QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.

そのため、前回の記事のように UTC でデータを送っている場合は、QuickSight でグラフを見るときに脳内で JST に変換して読むことになります。
これは「分析結果のグラフを国内以外からも見る」といったユースケースが BI 活用のシーンではありえる為かなと思います。

しかし「国内からしか見ないから JST で表示したい」というような場合を想定して、上記の仕様を踏まえて JST で表示する方法を考えてみました。

方法１：IoT Analytics の Lambda で元の時刻に９時間追加する
方法２：QuickSight 側で元の時刻に９時間追加する

いずれの方法でも、時刻が「＋９時間」されるだけで表示自体は UTC として表示されます。
しかし、UTC で表示されていることは一旦忘れてしまえば日本時間としてすんなり読むことができます。
ただ、時刻表記を９時間追加した時間で表示していても「QuickSight の表示は UTC」であることを忘れないようにしないと不要な混乱を招く可能性があるので注意が必要です

Amazon QuickSight で元の時刻に９時間追加する

さて、方法１の場合は Lambdaのコード上の話になるので、ここでは「方法２」について紹介したいと思います。
IoT Analytics や QuickSight の環境については前回の記事で使ったものをそのまま利用するので、既存のデータセットに対して作業を行います。

01-select-dataset

「データセットの編集」をクリックします。

02-edit-dataset

timestampというフィールドには UTC の時刻が入っています。

03-data-prep

ここで新たに「計算フィールド」を追加します。

$04-add-math-field$

新たに AddTime9h というフィールド名を付けて、下記のような計算式をセットします。

addDateTime(9, "HH", {timestamp})

21-add-time-9h

保存して元の画面に戻ると AddTime9h のフィールド値の時刻が９時間加算された時刻になっていることが分かります。時間を加算しただけなので、データタイプは「日付」のままです。

22-add-time-9h-field

グラフにしてみます。

23-publish-and-visualize

「日付」のデータタイプを持つフィールドが「timestamp」と「AddTime9h」の２つありますが、今回は「AddTime9h」を使って可視化できました。
これで「見かけ上」は JST でグラフ表示することができました。

24-visualize-dataset

さて、これだけでは記事の内容として少しさみしいので、今回検証した内容を追加でもう少し紹介したいと思います。

おまけ〜formatDate() と parseDate()を使う〜

先程は、addDateTime 関数を使って単純に元の時刻に９時間を加算していました。
しかし、下記の formatDate() のGitHub のドキュメントを見るとタイムゾーンを指定するオプションがあるようなので、これを利用してみたいと思います。

25-formatdate-timezone

先程と同様に既存のデータセットに対して、新たに timestampjst という名前の計算フィールドを作成して下記のような計算式をセットします。

parseDate(formatDate({timestamp}, "yyyy-MM-dd HH:mm:ss.SSS", 'Asia/Tokyo'), 'yyyy-MM-dd HH:mm:ss.SSS')

05-set-fomula

保存して元の画面に戻ると、timestampjst のフィールド値の時刻が JST になっていることが分かります。
また、timestampjst のデータタイプが「日付」として認識されていることも確認できました。
ただし、画像の通り「timestampjst」のフォーマットが yyyy-MM-dd'T'HH:mm:ss.SSSZ となっており QuickSight では UTC として認識されていることを抑えておきましょう。

06-utc-to-jst

このままグラフにしてみたいと思います。

07-visualize

「日付」のデータタイプを持つフィールドが「timestamp」と「timestampjst」の２つありますが、今回は「timestampjst」を使います。

09-visualize-graph

前回の記事と同じように可視化できました。

10-select-field

前回のグラフと比較して、グラフ上でも時刻表記が変わっていることが確認できました。
（しつこいですが、このグラフ上ではタイムゾーンを識別する情報がありませんが、内部では UTC として処理されていることを忘れないようにしましょう）

11-1150

12-0250

おまけ〜１つの計算式でformatDate() と parseDate()を使う〜

先程は１つの計算フィールドだけで一気に変換しましたが、分かりやすく２つの計算フィールドに分けてみたいと思います。
最初に１つ目の計算式を「timestamp2string」という名前で作成します。ここでは formatDate 関数だけを使って元のフィールド値 timestamp のデータを Asia/Tokyo のタイムゾーンに変換して文字列として返します。

formatDate({timestamp}, 'yyyy-MM-dd HH:mm:ss.SSS', 'Asia/Tokyo')

13-timesamp-to-string

formatDate 関数の書式は次のとおりです。

formatDate(date, ['format'])

formatDate は１つ目の引数で指定したフィールドの値を format で指定したフォーマットの日付に変換して文字列として返します。

例えば下記のようなデータを持つ「orderDate」というフィールド値がある場合、

orderDate      
=========
2012-12-14T00:00:00.000Z  
2013-12-29T00:00:00.000Z
2012-11-15T00:00:00.000Z

次の計算式で評価すると

formatDate(orderDate, 'dd MMM yyyy', 'America/Los_Angeles')

下記のような結果が返ってきます。またタイムゾーンも America/Los_Angeles になります。

13 Dec 2012
28 Dec 2013
14 Nov 2012

さて「timestamp2string」というフィールド値を追加しましたが、タイムゾーンが JST の時刻に変わっている事がわかります。またデータタイプが「文字列」になっています。
なお、変換に指定したフォーマットは元のフォーマットと変わっていないので、時刻データとしてのフォーマットは特に変わっていません。

14-field-timestamp2string

次に「date2jst」という２つ目のフィールド値を追加します。計算式は下記になります。
先程作成した timestamp2string を参照して yyyy-MM-dd HH:mm:ss.SSS にマッチするデータを探します。

parseDate({timestamp2string},"yyyy-MM-dd HH:mm:ss.SSS")

15-date2jst

２つ目の計算式では parseDate 関数を使っています。parseDate 関数の書式は次のとおりです。

parseDate(expression, ['format'])

parseDate 関数は次のような特徴があります。

expression の文字列を解析して日付の値を判断し、formatのパターンに一致する場合に、yyyy-MM-ddTkk:mm:ss.SSSZ形式（UTC）で返す。
マッチしない行はスキップされる
マッチしたデータのデータタイプは「日付」として返される
expression は文字列であること
サポートするパターンは下記ドキュメントに記載。

例えば下記のようなデータを持つ「prodDate」というフィールド値がある場合、

prodDate
--------
01-01-1999
12/31/2006
1/18/1982 
7/4/2010

次の計算式で評価すると

parseDate(prodDate, 'MM/dd/yyyy')

パターンにマッチした3行に対して結果が返ってきます。

12-31-2006T00:00:00.000Z
01-18-1982T00:00:00.000Z
07-04-2010T00:00:00.000Z

最終的に date2jst フィールドのデータのタイムゾーンが変わり、データタイプも「日付」として認識されていることが確認できました。
グラフ化する場合は date2jst フィールドを X 軸にすればいいですね。

16-field-date2jst

この２つの計算式を１つにしたものが、前半で紹介した次の式になります。
yyyy-MM-dd HH:mm:ss.SSS が２回出てきて少し分かりづらいですが、このように 2 つに分けて考えると分かりやすいかと思います。

parseDate(formatDate({timestamp}, "yyyy-MM-dd HH:mm:ss.SSS", 'Asia/Tokyo'), 'yyyy-MM-dd HH:mm:ss.SSS')

ちなみに、下記のようにするとスッキリしそうですが、元のフィールド値である timestamp のデータタイプが「日付」であるためエラーになります。

parseDate({timestamp}, 'yyyy-MM-dd HH:mm:ss.SSS', 'Asia/Tokyo')

また、次のように formatDate で変換するフォーマットを省略するとどうでしょうか？

parseDate(formatDate({timestamp}), "yyyy-MM-dd HH:mm:ss:SSS",'Asia/Tokyo')

formatDate 関数はデフォルトでは yyyy-MM-dd HH:mm:ss:SSS というフォーマットでデータを返すので、上記の通り yyyy-MM-dd HH:mm:ss:SSS フォーマットで parseDate 関数を使えばうまくいきそうに思えます。
非常に分かりづらいですが、秒とミリ秒の間の記号がコロン(:）になっています。

しかし、parseDate 関数は、yyyy-MM-dd HH:mm:ss:SSS 形式をサポートしていないため、この場合もエラーになり処理がスキップされてしまいます。（つまり何もデータが入らない）
そのため明示的に formatDate 関数で yyyy-MM-dd HH:mm:ss.SSS のフォーマットに変換して渡す必要があります。（秒とミリ秒の間の記号がピリオド（.））

ということで、やはり下記の計算式で処理することになります。

parseDate(formatDate({timestamp}, "yyyy-MM-dd HH:mm:ss.SSS", 'Asia/Tokyo'), 'yyyy-MM-dd HH:mm:ss.SSS')

もう少し一般化すると下記のようになります。

parseDate(formatDate({変換対象のフィールド名}, "変換したいフォーマット", 'Asia/Tokyo'), '変換したいフォーマットと同じフォーマット')

もし、こちらの方法を利用される場合は、ご利用環境に応じて上記の計算式を参考にしつつ、タイムスタンプのフォーマットを変えて使ってみていただければと思います。

タイムゾーンオプションについて

今回紹介した、formatDate(), parseDate() の２つの関数ですが、GitHub 上のドキュメントにはいずれもタイムゾーンオプションが書かれていました。
しかし、実際に試してみると formatDate() 関数でしか利用できず、parseDate で利用すると書式エラーとなってしまい計算フィールドの追加ができませんでした。

formatDate() でタイムゾーンを変換したデータは「文字列」のデータタイプになりますが、そのまま可視化する場合、単一のデータだけならグラフにプロットしても問題ありませんでした。一方で複数のデータを１つのグラフに表示しようとするとグラフが乱れてしまい、うまく可視化できませんでした。時刻フィールドが「日付」のデータタイプであれば複数データでも正しく可視化することができました。

時刻フィールドを「日付」のデータタイプにしたい場合は上記で見た通り、parseDate() も使う必要がありますが、parseDate() は UTC として時刻を返すので、formatDate() でタイムゾーンを変換しても parseDate() で UTC とみなされてしまうという問題がありました。

また、タイムゾーンオプションは GitHub には記載がありますが、AWS の公式ドキュメントには記載がありません。
このことからも、これらの関数においてはタイムゾーンオプションの利用は推奨されないのかもしれない点に注意が必要かと思います。

おまけ〜Lambda で JST に変換して QuickSight で参照する〜

冒頭で上げた「方法１」として「Lambda で元のデータに９時間を加算する」と書きました。
９時間加算するだけなら、QuickSight のaddDateTime() 関数と結果は変わりません。ここでは、Lambda で元のデータを JST に変換して送るとどうなるか、という検証を行います。

Lambda のコードは下記になります。

import json
import logging
import sys
import time
from datetime import datetime, timezone, timedelta

# Configure logging
logger = logging.getLogger()
logger.setLevel(logging.INFO)
streamHandler = logging.StreamHandler(stream=sys.stdout)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
streamHandler.setFormatter(formatter)
logger.addHandler(streamHandler)

# timezone
JST = timezone(timedelta(hours=+9), 'JST')

def lambda_handler(event, context):
    logger.info("event: {}".format(event))
    transformed = []
    
    for e in event:
    #for e, value in event.items():
        # e に単一アセットのデータが全部入る
        logger.info("e: {}".format(json.dumps(e, indent=2)))
        print(type(e)) #<class 'dict'>
        property_alias = e['propertyAlias']

        propertyValuesList = e['propertyValues'] # [{'value': {'doubleValue': 4.895646495792867}, 'timestamp': {'timeInSeconds': 1655307221, 'offsetInNanos': 157000000}, 'quality': 'GOOD'}]
        property_value = propertyValuesList[0] # {'value': {'doubleValue': 4.895646495792867}, 'timestamp': {'timeInSeconds': 1655307221, 'offsetInNanos': 157000000}, 'quality': 'GOOD'}

        value = ""
        if 'doubleValue' in property_value['value']:
            value = property_value['value']['doubleValue']
            logger.info("value_type: doubleValue")
        if 'integerValue' in property_value['value']:
            value = property_value['value']['integerValue']
            logger.info("value_type: integerValue")
        if 'booleanValue' in property_value['value']:
            value = property_value['value']['booleanValue']
            logger.info("value_type: booleanValue")
        if 'stringValue' in property_value['value']:
            value = property_value['value']['stringValue']
            logger.info("value_type: stringValue")

        quality = ""
        if 'quality' in property_value:
            quality = property_value['quality']
            logger.debug("quality in payload")
        
        timestamp = ""
        unixtime = ""
        nanoseconds = ""
        devicetime = ""
        if 'timestamp' in property_value and 'timeInSeconds' in property_value['timestamp'] and 'offsetInNanos' in property_value['timestamp']:
            inttime = property_value['timestamp']['timeInSeconds'] # 1655307221
            milliseconds = property_value['timestamp']['offsetInNanos'] / 1000000000 # 0.157
            unixtime = inttime + milliseconds # 1655307221.157
            dt = datetime.fromtimestamp(unixtime).replace(tzinfo=timezone.utc).astimezone(tz=JST)
            timestamp = dt.isoformat(timespec='milliseconds') # 2022-06-16T09:33:41.157+09:00

        if 'timestamp' in property_value and 'timeInSeconds' in property_value['timestamp'] and not 'offsetInNanos' in property_value['timestamp']:
            unixtime = property_value['timestamp']['timeInSeconds'] # 1655307221
            devicetime = datetime.fromtimestamp(unixtime) # '2022-06-15 15:33:41' (UTC)
            dt = datetime.fromtimestamp(unixtime).replace(tzinfo=timezone.utc).astimezone(tz=JST)
            timestamp = dt.isoformat(timespec='milliseconds') #2022-06-16T09:33:41.000+09:00

        row = {}
        row['propertyAlias'] = property_alias
        row['value'] = value
        row['timestamp'] = timestamp
        #row['timestamp'] = timestamp_jst
        row['quality'] = quality
        logger.debug("row: {}".format(row))
        transformed.append(row)

    logger.info("transformed: {}\n".format(json.dumps(transformed, indent=2)))
    return transformed # return は[]リストで返す

この Lambda により QuickSight へ「2022-06-16T09:33:41.157+09:00」というフォーマットの時刻データが渡されます。
これは、日本時間で「2022 年 6 月 16 日午前 9 時 33 分 41.157 秒」です。

この Lambda 関数の CloudWatch Logs では次のようなログが記録されていました。（見やすいように加工しています）
ログには時刻データが 2022-06-29T15:08:23.055+09:00 という JST のフォーマットに変換されていることが分かります。

2022-06-29T06:09:19.243000+00:00 2022/06/29/[$LATEST] [INFO]  2022-06-29T06:09:19.243Z  transformed: [
  {
    "propertyAlias": "/Factory/1/turbin/1/RPM",
    "value": 1417146631.0,
    "timestamp": "2022-06-29T15:08:23.055+09:00",
    "quality": "GOOD"
  }
]

一方で、QuickSight 上でこのデータを見ると、2022-06-29T06:08:23.055Z として UTC に変換されていることが分かりました。このようにタイムゾーンオフセットを持つ日付データの場合、QuickSight は UTC に変換して表示されることが分かりました。

これはドキュメントの記載どおりですが、ドキュメントには 2015-11-01T15:30:00 というフォーマットになる（UTC を表す末尾の「Z」が無い）と書かれています。実際には「yyyy-MM-ddTkk:mm:ss.SSSZ」のフォーマットになります。
細かいですが、個人的に気になっていた点も確認できました。

20-quicksight-change-jst-to-utc

改めて結論

結論として、Quicksight では時刻の表示は UTC になるので、タイムゾーンを指定した表示はできないことが分かりました。
BI という性格上、様々な地域から参照することも考えられるので、UTC で統一的に分析した方が扱いやすく、無理に JST に変更する必要はないかと思いました。
どうしても JST で参照したいという場合は、別途データを参照するクライアント側などでタイムゾーンを変更して処理するのがよいかと思います。