Quicksight - Parse a timestamp to extract and group hours

Question

I wanted to bin the timestamp column into periods, Currently we have a Timestamp column in the format of 'yyyy-MM-dd HH:mm:ss' and we are using calculated filed 'substring(formatDate({aggregate_ts}), 12, 5)' to extract the hours, here aggregate_ts is the column with timestamps

Is there anyway we can parse the timestamp to group them in bins such as 1pm to 5pm in 'Q1' and 5pm to 10pm in 'Q2'

Answer

Below mentioned is the Sample dataset that I have used :

sno	ts
A	2010-12-30 14:32:00
B	2010-12-30 15:06:00
C	2010-12-30 16:34:00
A	2011-01-06 09:18:00
D	2011-01-06 12:18:00
B	2011-01-06 01:18:00

Created a calculated field to extract the hour from the timestamp field "ts" as follows :

extract('HH', ts)

Created another calculated field "periodcalc" which groups based on the hours into different intervals as follows :

ifelse(hour<=5,'Q1', hour>5 AND hour<=10,'Q2', hour>10 AND hour<=15,'Q3', hour>15 AND hour<=20,'Q4', 'Q5')

Finally the result looks like this :

Enter image description here

AWS-User-3829235

answered 9 months ago

Quicksight - Parse a timestamp to extract and group hours

Relevant content