Skip to content
Search ⌘K
Get started

Time and continuous aggregates

Learn to work with timezones and continuous aggregates

Functions that depend on a local timezone setting inside a continuous aggregate are not supported. You cannot adjust to a local time because the timezone setting changes from user to user.

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.

Declare an explicit timezone

Section titled “Declare an explicit timezone”

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

  1. Create the view with an explicit timezone
    CREATE MATERIALIZED VIEW device_summary
    WITH (timescaledb.continuous)
    AS
    SELECT
      time_bucket('1 hour', observation_time) AS bucket,
      min(observation_time AT TIME ZONE 'EST') AS min_time,
      device_id,
      avg(metric) AS metric_avg,
      max(metric) - min(metric) AS metric_spread
    FROM
      device_readings
    GROUP BY bucket, device_id;
  2. Cast to a timestamp after the view

    Alternatively, you can cast to a timestamp using SELECT:

    SELECT min_time::timestamp FROM device_summary;

Integer-based time

Section titled “Integer-based time”

Date and time is usually expressed as year-month-day and hours:minutes:seconds. Most TimescaleDB databases use a PostgreSQL date/time-type column to express the date and time. However, in some cases, you might need to convert these common time and date formats to a format that uses an integer. The most common integer time is Unix epoch time, which is the number of seconds since the Unix epoch of 1970-01-01, but other types of integer-based time formats are possible.

These examples use a hypertable called devices that contains CPU and disk usage information. The devices measure time using the Unix epoch.

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 is 10 minutes.

Define the integer-based time column and chunk time interval:

CREATE TABLE devices(
  time BIGINT,        -- Time in minutes since epoch
  cpu_usage INTEGER,  -- Total CPU usage
  disk_usage INTEGER, -- Total disk usage
  PRIMARY KEY (time)
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time',
  tsdb.chunk_interval='10'
);

For TimescaleDB v2.23.0 and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column, TimescaleDB throws an error and asks for an explicit definition. For earlier versions, set partition_column to a time column.

If you are self-hosting TimescaleDB v2.20.0 to v2.22.1, to convert your data to the columnstore after a specific time interval, you have to call add_columnstore_policy after you call CREATE TABLE

If you are self-hosting TimescaleDB v2.19.3 and below, create a PostgreSQL relational table, then convert it using create_hypertable. You then enable hypercore with a call to ALTER TABLE.

To define a continuous aggregate on a hypertable that uses integer-based time, you need to have a function to get the current time in the correct format, and set it for the hypertable. You can do this with the set_integer_now_func function. It can be defined as a regular PostgreSQL function, but needs to be STABLE, take no arguments, and return an integer value of the same type as the time column in the table. When you have set up the time-handling, you can create the continuous aggregate.

  1. Set up the time conversion function
    CREATE FUNCTION current_epoch() RETURNS BIGINT
    LANGUAGE SQL STABLE AS $$
    SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)::bigint;$$;
    

     SELECT set_integer_now_func('devices', 'current_epoch');
  2. Create the continuous aggregate for the devices table
    CREATE MATERIALIZED VIEW devices_summary
    WITH (timescaledb.continuous) AS
    SELECT time_bucket('500', time) AS bucket,
       avg(cpu_usage) AS avg_cpu,
       avg(disk_usage) AS avg_disk
    FROM devices
    GROUP BY bucket;
  3. Insert some rows
    CREATE EXTENSION tablefunc;
    

    INSERT INTO devices(time, cpu_usage, disk_usage)
    SELECT time,
       normal_rand(1,70,10) AS cpu_usage,
      normal_rand(1,2,1) * (row_number() over()) AS disk_usage
    FROM generate_series(1,10000) AS time;

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

  4. Check the view data
    postgres=# SELECT * FROM devices_summary ORDER BY bucket LIMIT 10;
    bucket |       avg_cpu       |       avg_disk
    --------+---------------------+----------------------
         0 | 63.0000000000000000 |   6.0000000000000000
         5 | 69.8000000000000000 |   9.6000000000000000
        10 | 70.8000000000000000 |  24.0000000000000000
        15 | 75.8000000000000000 |  37.6000000000000000
        20 | 71.6000000000000000 |  26.8000000000000000
        25 | 67.6000000000000000 |  56.0000000000000000
        30 | 68.8000000000000000 |  90.2000000000000000
        35 | 71.6000000000000000 |  88.8000000000000000
        40 | 66.4000000000000000 |  81.2000000000000000
        45 | 68.2000000000000000 | 106.0000000000000000
    (10 rows)

Learn more

Section titled “Learn more”