Time and continuous aggregates

    To manage this, you can use explicit timezones in the view definition. Alternatively, you can create your own custom aggregation scheme for tables that use an integer time column.

    The most common method of working with timezones is to declare an explicit timezone in the view query.

    1. At the prompt, create the view and declare the timezone:

    2. Alternatively, you can cast to a timestamp after the view using SELECT:

      1. SELECT min_time::timestamp FROM device_summary;

    Integer-based time

    In these examples, we have a hypertable called devices that contains CPU and disk usage for devices. These devices measure time using microfortnights since epoch, under the humorous but impractical system of measurement called the .

    To create a hypertable that uses an integer-based column as time, you need to provide the chunk time interval. In this case, each chunk consists of a millifortnight, which is equivalent to 1000 microfortnights, or about twenty minutes.

    1. At the psql prompt, create a table and define the integer-based time column:

    2. Define the chunk time interval:

      1. SELECT create_hypertable('devices', 'time',
      3.   chunk_time_interval => 1000);

    1. At the psql prompt, set up a function to convert the time to the FFF system:

    2. Create the continuous aggregate for the devices table:

      1. CREATE MATERIALIZED VIEW devices_summary
      3. WITH (timescaledb.continuous) AS
      4. SELECT time_bucket('500', time) AS bucket,
      6.    avg(cpu_usage) AS avg_cpu,
      8.    avg(disk_usage) AS avg_disk
      10. FROM devices
      12. GROUP BY bucket;

    3. Insert some rows into the table:

      This command uses the tablefunc extension to generate a normal distribution, and uses the row_number function to turn it into a cumulative sequence.

      1. postgres=# SELECT * FROM devices_summary ORDER BY bucket LIMIT 10;
      3. bucket |       avg_cpu       |       avg_disk
      5. --------+---------------------+----------------------
      7.      0 | 63.0000000000000000 |   6.0000000000000000
      8.      5 | 69.8000000000000000 |   9.6000000000000000
      10.     10 | 70.8000000000000000 |  24.0000000000000000
      12.     15 | 75.8000000000000000 |  37.6000000000000000
      14.     20 | 71.6000000000000000 |  26.8000000000000000
      16.     25 | 67.6000000000000000 |  56.0000000000000000
      18.     30 | 68.8000000000000000 |  90.2000000000000000
      20.     35 | 71.6000000000000000 |  88.8000000000000000
      22.     40 | 66.4000000000000000 |  81.2000000000000000
      24.     45 | 68.2000000000000000 | 106.0000000000000000