> ## Documentation Index > Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Time and continuous aggregates > Work with timezones and integer-based time in continuous aggregates export const CAGG = 'continuous aggregate'; export const HYPERTABLE = 'hypertable'; export const HYPERCORE = 'hypercore'; export const PG = 'Postgres'; export const COLUMNSTORE = 'columnstore'; export const TIMESCALE_DB = 'TimescaleDB'; Functions that depend on a local timezone setting inside a {CAGG} 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 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** ```sql theme={"dark"} 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 timestamp at query time** Alternatively, you can cast to a timestamp after the view using `SELECT`: ```sql theme={"dark"} SELECT min_time::timestamp FROM device_summary; ``` ## Integer-based time Date and time is usually expressed as year-month-day and hours:minutes:seconds. Most {TIMESCALE_DB} databases use a [date/time-type][postgres-date-time] 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. 1. **Create a {HYPERTABLE} with an integer time column** Create a {HYPERTABLE} and define the integer-based time column and chunk time interval: ```sql theme={"dark"} 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 ( timescaledb.hypertable, timescaledb.partition_column='time', timescaledb.chunk_interval='10' ); ``` For {TIMESCALE_DB} [v2.23.0][tsdb-release-2-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, {TIMESCALE_DB} throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a time column. If you are self-hosting {TIMESCALE_DB} [v2.20.0][tsdb-release-2-23-0] to [v2.22.1][tsdb-release-2-23-0], to convert your data to the {COLUMNSTORE} after a specific time interval, you have to call [add\_columnstore\_policy][add_columnstore_policy] after you call [CREATE TABLE][hypertable-create-table] If you are self-hosting {TIMESCALE_DB} [v2.19.3][tsdb-release-2-19-3] and below, create a [{PG} relational table][pg-create-table], then convert it using [create\_hypertable][create_hypertable]. You then enable {HYPERCORE} with a call to [ALTER TABLE][alter_table_hypercore]. [pg-create-table]: https://www.postgresql.org/docs/current/sql-createtable.html [create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable [alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [chunk_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval [tsdb-release-2-23-0]: https://github.com/timescale/timescaledb/releases/tag/2.23.0 [tsdb-release-2-20-0]: https://github.com/timescale/timescaledb/releases/tag/2.20.0 [tsdb-release-2-22-1]: https://github.com/timescale/timescaledb/releases/tag/2.22.1 [tsdb-release-2-19-3]: https://github.com/timescale/timescaledb/releases/tag/2.19.3 To define a {CAGG} 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`][set_integer_now_func] function. It can be defined as a regular {PG} function, but needs to be [`STABLE`][pg-func-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 {CAGG}. 1. **Set up a function to convert the time to the Unix epoch** ```sql theme={"dark"} 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 {CAGG} for the `devices` table** ```sql theme={"dark"} 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 into the table** ```sql theme={"dark"} 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 that the view contains the correct data** ```sql theme={"dark"} 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) ``` [pg-func-stable]: https://www.postgresql.org/docs/current/sql-createfunction.html [postgres-date-time]: https://www.postgresql.org/docs/current/datatype-datetime.html [set_integer_now_func]: /api-reference/timescaledb/hypertables/set_integer_now_func