TimescaleDB 时序数据库 之三 create_hypertable

os: ubuntu 16.04
db: postgresql 10.6

版本

# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.5 LTS
Release:	16.04
Codename:	xenial
#
# su - postgres
$ psql -c "select version();"
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

使用

postgres=# DROP TABLE IF EXISTS conditions;
postgres=# CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);
postgres=# CREATE INDEX ON conditions(time DESC);
postgres=# 
postgres=# SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);

86400000000 is in usecs and is equal to 1 day: interval ‘1 day’

postgres=# INSERT INTO conditions(time, location, temperature, humidity)
  VALUES (NOW(), 'office', 70.0, 50.0);

postgres=# SELECT * FROM conditions ORDER BY time DESC LIMIT 100;

postgres=# SELECT time_bucket('15 minutes', time) AS fifteen_min,
    location, COUNT(*),
    MAX(temperature) AS max_temp,
    MAX(humidity) AS max_hum
  FROM conditions
  WHERE time > NOW() - interval '3 hours'
  GROUP BY fifteen_min, location
  ORDER BY fifteen_min DESC, max_temp DESC;
  

参考:
https://github.com/timescale/timescaledb

https://www.timescale.com/
https://docs.timescale.com/v1.2/main
https://docs.timescale.com/v1.2/tutorials/other-sample-datasets