I have an application that stores telematics from vehicles across the USA. The USA has multiple time zones and the vehicle can be active and move across time zones whilst active. I want to be able to query 'yesterday' and calculate all the 'active time' all vehicles have been active for.
All 'time' in Amazon Timestream is stored in UTC as a timestamp so I cannot add the timezone offset to the time.
Raw data:
| vehicle | totalHours | time |
|---|---|---|
| abc | 10 | 2021-03-20T20:00:00 EST |
| abc | 20 | 2021-03-21T10:00:00 EST |
| abc | 30 | 2021-03-21T18:00:00 EST |
| def | 40 | 2021-03-21T02:00:00 EST |
| def | 50 | 2021-03-21T15:00:00 EST |
| def | 60 | 2021-03-21T20:00:00 EST |
Stored data:
| vehicle | measure_name | measure_value::double | time |
|---|---|---|---|
| abc | totalHours | 10 | 2021-03-21 01:00:00 |
| abc | totalHours | 20 | 2021-03-21 15:00:00 |
| abc | totalHours | 30 | 2021-03-21 23:00:00 |
| def | totalHours | 40 | 2021-03-21 10:00:00 |
| def | totalHours | 50 | 2021-03-21 23:00:00 |
| def | totalHours | 60 | 2021-03-22 04:00:00 |
Query:
SELECT vehicle, SUM('measure_value::double') AS totalHours
FROM "Database"."Table"
WHERE time BETWEEN '2021-03-18 00:00:00' AND '2021-03-18 23:59:59' AND measure_name = 'totalHours'
GROUP BY vehicle
Expected:
| vehicle | totalHours |
|---|---|
| abc | 50 |
| def | 150 |
Returns:
| vehicle | totalHours |
|---|---|
| abc | 60 |
| def | 90 |
- If I query in UTC time, it will be 5-8 hours out of date.
- If I query in Eastern Time (UTC+5) then any vehicles running in California (UTC+8) will have their data up to 4 hours out.
How would I query yesterday in all time zones without querying all time zones independently?
