cnosdb

How to count the number of data entries for the current week?

I am using CnosDB to store temporal data, and I would like to write a query to count the number of data items in the current week. My temporal data is stored in a sensor named sensor_data In the table of data, this table contains the following columns:

time
sensor_id
metrics_value

I need to write an SQL query to calculate the number of data entries for the current week

Solution

  • You can use function date_bin(interval, source, origin) to get the start of a time bucket, you may want to get the first day of one week(arg origin should be the Monday):

    SELECT date_bin(INTERVAL '1' week, TIMESTAMP '2023-09-27T09:10:11', TIMESTAMP '2023-09-04T00:00:00') AS start;
+---------------------+
| start               |
+---------------------+
| 2023-09-25T00:00:00 |
+---------------------+

    Use <start> + interval '1 week' as end to get the end:

    SELECT date_bin(INTERVAL '1' week, TIMESTAMP '2023-09-27T09:10:11', TIMESTAMP '2023-09-04T00:00:00') + INTERVAL '5 days' AS end;
+---------------------+
| end                 |
+---------------------+
| 2023-09-30T00:00:00 |
+---------------------+

    Now you can use the time range between '2023-09-25T00:00:00' and '2023-09-30T00:00:00' in your where clause.

    SELECT time, sensor_id, metrics_value
FROM "the table of data"
WHERE
      time >= date_bin(INTERVAL '1' week, now(), TIMESTAMP '2023-09-04T00:00:00')
      AND time <= date_bin(INTERVAL '1' week, now(), TIMESTAMP '2023-09-04T00:00:00') + INTERVAL '5 days';