时间和连续聚合

不支持依赖于连续聚合内部本地时区设置的函数。您不能调整为本地时间，因为时区设置因用户而异。

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

声明显式时区

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

声明显式时区

在 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 纪元时间，它是自 1970-01-01 的 Unix 纪元以来的秒数，但其他类型的基于整数的时间格式也是可能的。

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

要创建使用基于整数的列作为时间的 hypertable（超表），您需要提供 chunk（块）时间间隔。在本例中，每个 chunk（块）为 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)
);

定义 chunk（块）时间间隔


SELECT create_hypertable('devices', by_range('time', 10));

要在使用基于整数的时间的 hypertable（超表）上定义连续聚合，您需要有一个函数来获取当前时间的正确格式，并为 hypertable（超表）设置它。您可以使用 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)

时间和连续聚合

声明显式时区

声明显式时区

基于整数的时间

创建具有自定义基于整数的时间列的表

创建具有基于整数的时间的连续聚合

相关内容