1

I want to use aws timestream to track user requests to our API. In a first step I just store the user id for each request to the API. For a chart I want to get number of requests by user in certain time intervals. This SQL gets requests from user 2 within last two hours in intervals of 10 minutes:

SELECT user_id, COUNT(user_id) AS n, bin(time, 10m) AS btime
FROM db_api.t_access
WHERE time > ago(2h) AND user_id IN ('2')
GROUP BY user_id, bin(time, 10m)
ORDER BY btime DESC

Result is:

2021-03-06 00:20:00.000000000 user_id:2 Count:1
2021-03-06 00:10:00.000000000 user_id:2 Count:4
2021-03-05 23:40:00.000000000 user_id:2 Count:2

Intervals without requests will not be returned, here 00:00 and 23:50. Is there any way to get this empty intervals with Count = 0 in SQL? Or do I have to write my own code to get all intervals from result set (I use the aws PHP API)?

Fabian_Z071
  • 275
  • 1
  • 11

1 Answers1

0

You can use:

 interpolate_fill(timeseries, array[timestamp], double)

Ref link: https://docs.aws.amazon.com/timestream/latest/developerguide/timeseries-specific-constructs.functions.interpolation.html

4b0
  • 21,981
  • 30
  • 95
  • 142
oboiko
  • 1