---
title: Time and continuous aggregates | Tiger Data Docs
description: Learn to work with timezones and continuous aggregates
---

In TimescaleDB v2.20.0 and higher, a continuous aggregate that uses a function depending on the session timezone is created with a warning. Its results can change on rematerialization, because the setting changes from user to user. Inside `time_bucket`, and in earlier versions, such a function is an error.

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

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

```
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;
```

Alternatively, store the `timestamptz` value and convert it when you query the view:

```
CREATE MATERIALIZED VIEW device_summary_tz
WITH (timescaledb.continuous)
AS
SELECT
  time_bucket('1 hour', observation_time) AS bucket,
  min(observation_time) AS min_time,
  device_id
FROM
  device_readings
GROUP BY bucket, device_id;


SELECT min_time::timestamp FROM device_summary_tz;
```

The cast uses the timezone of the session that runs the query.

## 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](https://www.postgresql.org/docs/current/datatype-datetime.html) 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.

For an integer-based time column, set `tsdb.chunk_interval` in the units of the column. In this case, each chunk covers one day.

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

```
CREATE TABLE devices(
  time BIGINT,        -- Time in seconds 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='86400'
);
```

For TimescaleDB [v2.23.0](https://github.com/timescale/timescaledb/releases/tag/2.23.0) and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. To partition on another column, set `tsdb.partition_column`. If the table has no timestamp column, TimescaleDB throws an error and asks for an explicit definition. For earlier versions, set `tsdb.partition_column` to a time column.

If you are self-hosting TimescaleDB [v2.20.0](https://github.com/timescale/timescaledb/releases/tag/2.20.0) to [v2.22.1](https://github.com/timescale/timescaledb/releases/tag/2.22.1), to convert your data to the columnstore after a specific time interval, you have to call [add\_columnstore\_policy](/reference/timescaledb/hypercore/add_columnstore_policy/index.md) after you call [CREATE TABLE](/reference/timescaledb/hypertables/create_table/index.md)

If you are self-hosting TimescaleDB [v2.19.3](https://github.com/timescale/timescaledb/releases/tag/2.19.3) and below, create a [PostgreSQL relational table](https://www.postgresql.org/docs/current/sql-createtable.html), then convert it using [create\_hypertable](/reference/timescaledb/hypertables/create_hypertable/index.md). You then enable hypercore with a call to [ALTER TABLE](/reference/timescaledb/hypercore/alter_table/index.md).

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`](/reference/timescaledb/hypertables/set_integer_now_func/index.md) function. It can be defined as a regular PostgreSQL function, but needs to be [`STABLE`](https://www.postgresql.org/docs/current/sql-createfunction.html), 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. **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.

3. **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;
   ```

4. **Check the view data**

   ```
   SELECT * FROM devices_summary ORDER BY bucket LIMIT 10;
   ```

   You see something like:

   ```
    bucket |       avg_cpu       |       avg_disk
   --------+---------------------+-----------------------
         0 | 69.8977955911823647 |  510.9739478957915832
       500 | 69.6340000000000000 | 1586.3920000000000000
      1000 | 70.4360000000000000 | 2489.7040000000000000
      1500 | 69.5820000000000000 | 3424.7480000000000000
      2000 | 69.7120000000000000 | 4382.1320000000000000
      2500 | 69.7220000000000000 | 5596.9300000000000000
      3000 | 69.6380000000000000 | 6453.6740000000000000
      3500 | 70.6200000000000000 | 7260.8820000000000000
      4000 | 70.1320000000000000 | 8611.2260000000000000
      4500 | 69.7580000000000000 | 9362.2100000000000000
   (10 rows)
   ```

## Learn more

- [Understand continuous aggregates](/learn/continuous-aggregates/index.md): How continuous aggregates work, JOINs, and function support.
- [Create a continuous aggregate](/build/continuous-aggregates/create-a-continuous-aggregate/index.md): Step-by-step guide to creating and querying a continuous aggregate.
- [Refresh continuous aggregates](/build/continuous-aggregates/refresh-policies/index.md): Set up automatic refresh policies.
- [Understand time buckets](/learn/data-lifecycle/time-buckets/about-time-buckets/index.md): How time bucket origins and timezones work.
- [`CREATE MATERIALIZED VIEW` reference](/reference/timescaledb/continuous-aggregates/create_materialized_view/index.md): Full API reference.
