postgresql - 在 Postgres 8.4 中使用纪元时间获取每天的最小值/最大值

Question

我有一张包含纪元值(每分钟一个，纪元本身以毫秒为单位)和温度的表格。

select * from outdoor_temperature order by time desc;
     time      | value
---------------+-------
 1423385340000 |  31.6
 1423385280000 |  31.6
 1423385220000 |  31.7
 1423385160000 |  31.7
 1423385100000 |  31.7
 1423385040000 |  31.8
 1423384980000 |  31.8
 1423384920000 |  31.8
 1423384860000 |  31.8
 [...]

我想获取每天发生的最低值(和最高值，但这可以是一个单独的查询)，以及发生的具体时间(最好是原始纪元时间)。我已经设法用 date_trunc 做到了，但这给了我一般的一天，而不是那天的具体时间:

select
    date_trunc('day',TIMESTAMP WITH TIME ZONE 'epoch' + (time/1000) * INTERVAL '1 second') as timestamp,
    min(value)
from outdoor_temperature
group by timestamp
order by min asc
limit 5;

       timestamp        | min
------------------------+------
 2015-03-27 00:00:00+10 | 10.7
 2015-03-28 00:00:00+10 | 10.8
 2015-01-30 00:00:00+10 | 13.6
 2015-03-17 00:00:00+10 | 14.0
 2015-03-29 00:00:00+10 | 14.5
(5 rows)

我需要做某种加入魔法吗(我的加入魔法非常弱)，还是我从完全错误的方向攻击它？我试过 DISTINCT ON 但没成功。

Answer 1

您可以从这个查询开始:

SELECT  date_trunc('minute',TIMESTAMP WITH TIME ZONE 'epoch' + (time/1000) * INTERVAL '1 second') as timestamp, value AS temperature from _outdoor_temperature

显示两列，第一列是“纪元”转换为具有“分钟”精度的时间戳。由于您需要找到每一天的最低/最高值，因此如果列中也只有日期而不是时间戳，那就太好了:

SELECT
x.timestamp::date AS a,
x.timestamp AS b,
temperature AS c
FROM (
SELECT  date_trunc('minute',TIMESTAMP WITH TIME ZONE 'epoch' + (time/1000) * INTERVAL '1 second') as timestamp, value AS temperature from _outdoor_temperature
) AS x

现在您有一个日期作为“a”列，一个时间戳作为“b”列，最后一个“c”列是温度值。

最后一部分是结合使用“order by”和“distinct on”表达式。这比分组依据更好，因为您正在查找一列的唯一值并查看另一列的关联:

select distinct on(y.a)
y.a,
y.b,
y.c
from (
SELECT
x.timestamp::date AS a,
x.timestamp AS b,
temperature AS c
FROM (
SELECT  date_trunc('minute',TIMESTAMP WITH TIME ZONE 'epoch' + (time/1000) * INTERVAL '1 second') as timestamp, value AS temperature from _outdoor_temperature
) AS x
) y

order by y.a, y.c