Get 1 Year results from TimescaleDB

Question

I have a table with data for IoT Devices, sending measurements every hour. Have to get reports for Today, Week, Month and Year.

Using timescaleDB i got some "ok" results for Today, Week and Month. For example Monthly:

   SELECT
      time_bucket('4 weeks', measured_at) AS month,
      MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
      FROM readings
      WHERE device_id = 4
      GROUP BY month
      ORDER BY month DESC LIMIT 12;

But can not find a good way to fetch values for years? Any one ever tried that?

Year is not supported by timescale, using weeks results in wrong result.

ERROR: interval defined in terms of month, year, century etc. not supported

What is exactly the problem? Doestn't using `'1 year'` instead of `'4 weeks'` work? — Eelke, Apr 22 '19 at 07:01
year is not supported: ERROR: interval defined in terms of month, year, century etc. not supported — opHASnoNAME, Apr 22 '19 at 08:55

score 4 · Accepted Answer · answered Apr 22 '19 at 17:01

Please make sure you know which data you're actually aggregating with time_bucket:

TIMESTAMPTZ arguments are bucketed by the time at UTC. So the alignment of buckets is on UTC time. One consequence of this is that daily buckets are aligned to midnight UTC, not local time.

As already pointed to by @TmTron the actual correct version for months and years would be to use date_trunc as follows:

SELECT
    date_trunc('month', measured_at) AS month,
    MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
    FROM readings
    WHERE device_id = 4
    GROUP BY 1
    ORDER BY 1 DESC LIMIT 12;

...and:

SELECT
    date_trunc('year', measured_at) AS year,
    MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
    FROM readings
    WHERE device_id = 4
    GROUP BY 1
    ORDER BY 1 DESC LIMIT ...;

And if you only select a certain interval (e.g. last 12 months), please always add a criteria to reduce the number of partitions to scan, e.g.:

SELECT
    date_trunc('month', measured_at) AS month,
    MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
    FROM readings
    WHERE device_id = 4
        AND measured_at >= CURRENT_TIMESTAMP - '13 months'::interval
    GROUP BY 1
    ORDER BY 1 DESC LIMIT 12;

score 3 · Answer 2 · answered Apr 22 '19 at 10:07

for now time_bucket does not support month/year: see #414

time_bucket is designed for regular intervals like days, hours, minutes. As months and years are variable time units, the function does not support them

postgres date_trunc also supports month/year

Get 1 Year results from TimescaleDB

2 Answers2