sql - 基于unix时间戳聚合数据创建数据库

Question

我对 SQL 和时间序列数据库还很陌生。我正在使用 crate 数据库(它认为使用的是 PostgreSQL)。我想按小时、天、周和月聚合数据。 Unix 时间戳用于存储数据。以下是我的示例数据库。

|sensorid | reading    | timestamp|
====================================
|1        | 1604192522 | 10       |
|1        | 1604192702 | 9.65     |
|2        | 1605783723 | 8.1      |
|2        | 1601514122 | 9.6      |
|2        | 1602292210 | 10    |
|2        | 1602291611 | 12    |
|2        | 1602291615 | 10    |

我尝试使用 FROM_UNIXTIME 不支持的 sql 查询。请帮帮我？

我正在寻找每小时数据的答案如下。

传感器，读数，时间戳

1          19.65(10+9.65)        1604192400(starting hour unixt time)
2           8.1                  1605783600(starting hour unix time)
2           9.6                  1601514000(starting hour unix time)
2           32 (10+12+10)        1602291600(starting hour unix time)

我正在寻找月度数据的答案，就像

sensorid  ,     reading  ,               timestamp
1           24.61(10+9.65+8.1)       1604192400(starting month unix time)
2           41.6(9.6+10+12+10)       1601510400(starting month unix time)

Answer 1

一个直接的方法是:

SELECT
    (date '1970-01-01' + unixtime * interval '1 second')::date as date,
    extract(hour from date '1970-01-01' + unixtime * interval '1 second') AS hour,
    count(c.user) AS count
FROM core c
GROUP BY 1,2 

如果您满足于将日期和时间放在同一列中(这对我来说似乎更有帮助)，您可以使用 date_trunc():

select 
    date_trunc('hour', date '1970-01-01' + unixtime * interval '1 second') as date_hour,
    count(c.user) AS count
FROM core c
GROUP BY 1,2