Get min/max value per day with epoch times in Postgres 8.4

Question

I have a table with epoch values (one per minute, the epoch itself is in milliseconds) and temperatures.

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
 [...]

I want to get the lowest value (and highest, but that can be a separate query) that occurred in each day, and the specific time (preferably the original epoch time) when that occurred. I've managed to do it with date_trunc but that gives me the general day, rather than the specific time within that day:

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)

Is there some sort of join magic I need to do (my join-fu is extremely weak), or am I attacking this from totally the wrong direction? I tried DISTINCT ON but didn't manage to even get that working.

Answer 1

You can start from this query:

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

which shows two columns, the first is "epoch" converted to the timestamp with "minute" precision. Since you need to find the lowest/highest value for each day, would be nice to have also column with just a date rather than timestamp:

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

So now you have a date as "a" column, a timestamp as "b" column and the temperature value in the last, "c" column.

The last part is to use "order by" in conjunctionw ith "distinct on" expression. This is better than group by, because you're finding unique values of one column and see the associations of another:

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