Get monthly sum/agv/max of a measurement from timestream

I am storing measurements in Timestream with the following attributes (example):

@2021-01-04 00:00:00 | DIMENSIONS                 | MEASURES
                     | domain: www.foo.com        | uniq_users: 9
                     | layer: none                | request_count: 11
                     | status: successful         | bytes_sent: 18097
                     | cache_status: Hit          |

Let’s assume these metrics are written one per day. How do I have to query the data in order to get a monthly sum on the request count metric for a specific domain?

In order to get a sum for the whole time range queried I can do this:

WITH per_day AS (
    SELECT 
       time, 
       domain,
       measure_value::double AS request_count
    FROM 
        "database"."table"
    WHERE 
        time between '2021-01-01 00:00:00' and '2022-01-01 00:00:00'
        AND measure_name = 'request_count'
        AND domain = 'www.foo.com'
    ORDER BY time ASC
)
SELECT sum(request_count)
AS total
FROM per_day

This returns a total of the range specified. Is there a way to get the the total per month (via GROUP BY or similar)?

Answer

You can truncate a timestamp to different precisions using date_trunc in most SQL languages, AWS Timestream included.

In your case, you want to truncate time to a monthly precision: date_trunc('month', time). You can then group by this variable and sum request_count.

SELECT 
   date_trunc('month', time) AS month, 
   SUM(measure_value::double) AS request_count
FROM 
    "database"."table"
WHERE 
    time between '2022-01-01 00:00:00' and '2022-01-02 00:00:00'
    AND measure_name = 'request_count'
    AND domain = 'www.foo.com'
GROUP BY 1
ORDER BY month ASC