Amazon Quicksight date field granularity - SECOND level aggregation

user_923410 0
Viewed 1114

I have a dataset with millisecond epoch timestamps. I have converted these to datetime types and can build visuals with data bucketed in 1 minute intervals by setting the date field granularity in the field well to MINUTE. However, I need to visualise the data to 1 second precision. Is there a way to do this today or is it coming soon?

As a (very poor) alternative, I have tried using the epoch millis timestamp (integer) as the X axis, which gives me the granularity/detail I require. However, this is a pretty bad solution as the users need to get familiar with an online epoch convertor when they want to record a timestamp.

To illustrate this, these two graphs are both displaying exactly the same dataset.

  • Graph 1: X axis: date ts ASC (bucketed by minute); Value: decimal value AVG
  • Graph 2: X axis: int epochts ASC; Value: decimal value AVG

A tale of two graphs

Perhaps not surprisingly, they look totally different. The first has a linear scale, as QuickSight understands dates. The second does not have a linear scale but instead sequentially lists out the epoch times in ascending order. As there are far more data points towards the end of the time period, you end up with a highly skewed chronological view. Neither of the views of the data are acceptable to the customer. But what can I do other than use a different BI tool?

amazon-quicksight amazon-athena business-intelligence
1 Answers

Not a perfect solution, but a slightly better hack. Something I've used quite a bit with QS is to use a reverse date as an Integer for the x axis. Not ideal, but can be used to make slightly more user friendly charts:

Something like. ts =

extract("YYYY",time) * 10000000000 + 
extract("MM",time) * 100000000 + 
extract("DD",time) * 1000000 + 
extract("HH",time) * 10000 + 
extract("MM",time) * 100 + 
extract("SS",time)

To make it more human readable, you could just use formateDate() instead of the multipliers if a string works. Depends on the visuals you want to use it with.

user_149486310
Related