I am storing measurements in Timestream with the following attributes (example):
x
@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)?
Advertisement
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