1

Using date_trunc or bin we can round to hour and then use groupBy, but I am not able to give a timezone option for this. Any workaround?

Riddhesh Ganatra
  • 11
  • 3

2 Answers2

0

According to iso-8601 you can define your timezone in a text manner like a

2020-02-08 09+07:00 — As format +-HH:mm This date time has an offset of seven hours from UTC. In other words it’s at 9 a.m. in time zone +7.

so, using from_iso8601_timestamp function you can convert (string) datetime+timezone and got timestamp in UTC you need

all functions are here: https://docs.aws.amazon.com/timestream/latest/developerguide/date-time-functions.html

in my case an example is

-- group by including timezone GMT+3
SELECT measure_name,
    from_iso8601_timestamp(concat(to_iso8601(date_trunc('hour', time)), '-03:00')) as iso8601_TZ, 
    count(measure_name)
FROM "sampleDB"."DevOpsMulti" 
WHERE time >= from_iso8601_timestamp('2022-05-20T09:00+03:00')
GROUP BY measure_name, from_iso8601_timestamp(concat(to_iso8601(date_trunc('hour', time)), '-03:00'))
ORDER BY iso8601_TZ DESC 
LIMIT 10;

result : https://i.stack.imgur.com/KbIcT.png

Conclusion: little bit overheaded as for me

Fedor Mokhon
  • 1
0

Had the same problems, solved it by using:

bin(date_add('hour', 18, time), 1day)

if the timezone was 18 hours off from UTC. This works perfectly in "group by" clauses etc.

Documentation: AWS Timestream Documentation - Interval and Duration functions

Günther
  • 1
  • 1