时间和连续聚合

Timescale Cloud：性能、规模、企业级

自托管产品

MST

连续聚合中不支持依赖本地时区设置的函数。由于时区设置因用户而异，因此无法调整到本地时间。

为了管理这一点，您可以在视图定义中使用显式时区。或者，您可以为使用整数时间列的表创建自己的自定义聚合方案。

声明显式时区

处理时区最常见的方法是在视图查询中声明显式时区。

在 psql 提示符下，创建视图并声明时区


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;

或者，您可以使用 SELECT 在视图之后转换为时间戳。
```
SELECT min_time::timestamp FROM device_summary;
```

基于整数的时间

日期和时间通常表示为年-月-日和时:分:秒。大多数 Timescale 数据库使用日期/时间类型列来表示日期和时间。但是，在某些情况下，您可能需要将这些常见的日期和时间格式转换为使用整数的格式。最常见的整数时间是 Unix 纪元时间，它是自 Unix 纪元 1970-01-01 以来的秒数，但也可以是其他类型的基于整数的时间格式。

这些示例使用一个名为 devices 的超表，其中包含 CPU 和磁盘使用信息。这些设备使用 Unix 纪元测量时间。

要创建使用基于整数的列作为时间的超表，您需要提供块时间间隔。在此示例中，每个块为 10 分钟。

在 psql 提示符下，创建超表并定义基于整数的时间列和块时间间隔


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'
);

如果您是自托管 TimescaleDB v2.19.3 及以下版本，请创建一个PostgreSQL 关系表，然后使用 create_hypertable 进行转换。然后通过调用 ALTER TABLE 启用 hypercore。

要在使用基于整数的时间的超表上定义连续聚合，您需要有一个函数以正确格式获取当前时间，并将其设置为超表。您可以使用 set_integer_now_func 函数来完成此操作。它可以定义为常规 PostgreSQL 函数，但需要是 STABLE、不带参数，并返回与表中时间列相同类型的整数值。设置好时间处理后，即可创建连续聚合。

在 psql 提示符下，设置一个函数将时间转换为 Unix 纪元


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');

为 devices 表创建连续聚合


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;

向表中插入一些行


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;

此命令使用 tablefunc 扩展生成正态分布，并使用 row_number 函数将其转换为累积序列。

检查视图是否包含正确的数据


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)

时间和连续聚合

声明显式时区

基于整数的时间

相关内容