基础操作

There are a few basic operations that you will be using frequently with hypertables within TimescaleDB: ​inserting data​, ​querying data​, and ​indexing data​. We are assuming here that you have already generated a hypertable by ​creating one​ or ​migrating your data​.

TimescaleDB的hypertables内部有一些频繁使用的操作：插入数据，数据查询，数据索引。假定你已经新建了一个hypertable或者已经将数据迁移进了hypertable。

插入 & 查询

Inserting data into the hypertable is done via normal SQL ​INSERT​ commands, e.g. using millisecond timestamps:

使用普通SQL的INSERT命令将数据插入hyper table，例如：使用毫秒时间戳：

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

Similarly, querying data is done via normal SQL ​SELECT​ commands.

同样地，使用普通SQLSELECT命令查询数据。

SELECT * FROM conditions ORDER BY time DESC LIMIT 100;

SQL ​UPDATE​ and ​DELETE​ commands also work as expected. For more examples of using TimescaleDB's standard SQL interface, please see our API page (​INSERT​, ​SELECT​).

SQL中的​UPDATE​ 和 ​DELETE命令也可以正常使用。更多TimescaleDB的标准SQL接口的使用示例请查阅API页面。

添加数据索引

Data is indexed via the SQL ​CREATE INDEX​ command. For instance,

通过SQL的​CREATE INDEX命令为数据创建索引，例如：

CREATE INDEX ON conditions (location, time DESC);

This can be done before or after converting the table to a hypertable.

无论将表转化为hypertable与否，都可以进行上述操作。

添加Suggestions索引

Our experience has shown that for time-series data, the most-useful index type varies depending on your data.

经验表明，最有用的时序数据的索引类型取决于数据类型。

For indexing columns with discrete (limited-cardinality) values (e.g., where you are most likely to use an "equals" or "not equals" comparator) we suggest using an index like this (using our hyper table ​conditions​ for the example):

为有离散值（有限基数）的数据列添加索引时（例如，你应该会使用"equals" 或者 "not equals" 比较器），我们建议使用如下索引（以conditions​为例）。

CREATE INDEX ONconditions(location, time DESC);

For all other types of columns, i.e., columns with continuous values (e.g., where you are most likely to use a "less than" or "greater than" comparator) the index should be in the form:

为其他类型的数据列-有连续值的列添加索引时（你应该会使用"less than" 或者"greater than"比较器），我们建议以如下方式添加索引：

CREATE INDEX ON conditions (time DESC, temperature);

Having a ​time DESC​ column specification in the index allows for efficient queries by column-value and time. For example, the index defined above would optimize the following query:

通过列值和时间可以有效查询有​time DESC​索引的数据列。例如：上面定义的索引可以优化以下查询语句：

SELECT * FROM conditions WHERE location = 'garage'
  ORDER BY time DESC LIMIT 10

For sparse data where a column is often NULL, we suggest adding a ​WHERE column IS NOT NULL​ clause to the index (unless you are often searching for missing data). For example,

对于数据列经常为空的稀疏数据来说，我们建议在索引中加入​WHERE column IS NOT NULL​语句。（除非你经常查询缺失数据）。例如：

CREATE INDEX ON conditions (time DESC, humidity)
  WHERE humidity IS NOT NULL;

this creates a more compact, and thus efficient, index.

这样就创建了一个简洁高效的索引。

TIP: To a define an index as UNIQUE or PRIMARY KEY, the time column and, if it exists, the partitioning column ​must​ be the first (or first two) columns that comprise the index. That is, using our running example, you can define a unique index on just the {time, location} fields, or to include a third column (say, temperature), the index must be specified as {time, location, temperature}. That said, we find UNIQUEindexes in time-series data to be much less prevalent than in traditional relational data models.

提示：时间列和分区列（如果存在的话）必须是前两个包含唯一键或者主键索引的列。以我们的例子来说，你必须在{time, location}列定义唯一键索引，或者可以添加第三个列（温度），将索引定义为{time, location, temperature}。也就是说，唯一键索引普遍是定义在传统的关系型数据模式中的而不是时间序列数据上。

默认索引

By default, TimescaleDB automatically creates a time index on your data when a hypertable is created.

默认情况下，hypertable创建完成后TimescaleDB会自动为数据创建时间索引。

CREATE INDEX ON conditions (time DESC);

Additionally, if the ​create_hypertable​ command specifies an optional "space partition" in addition to time (say, the ​location​ column), TimescaleDB will automatically create the following index:

此外，除了时间索引以外，如果​create_hypertable方法还指定了其他“空间分区”（例如：the ​location​ column），TimescaleDB会自动创建如下索引：

CREATE INDEX ON conditions (location, time DESC);

This default behavior can be overridden when executing the ​create_hypertable​ command (​see the API docs​).

执行create_hypertable​命令时，这种默认行为会被重写。

​Next​: If you would like to see what you can do with a full data set, you can check out our ​basic tutorial​ or play around on your own with our ​sample datasets​.

你可以查询我们的基础指南或者操作我们的样本数据库来测试你的数据集。