Postgres - using aggregate functions to return a different column?

Question

I currently have a table containing raw temperature data that looks like this:

I have written a query to find the min, max and avg of this data grouped by the date of the timestamp:

drop table solardb.weatherinfodaily;

select date_trunc('day',ts) as ts,
min("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Min Temp (F)",
avg("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Avg Temp (F)",
max("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Max Temp (F)"
into solardb.weatherinfodaily
from solardb.weatherdata
group by date_trunc('day',ts) 
order by date_trunc('day',ts);

select * from solardb.weatherinfodaily;

I would like to return the timestamp that is associated with the aggregate function and I have not figured out a way to do this yet.

i.e. the query would return:

ts, Daily Min Temp, timestamp from raw data table when the min temp happened, daily avg temp, daily max temp, timestamp from raw data when max temp happened.

How can I write such a query?

Answer 1

You need to self-join the results of your current query with the base table (twice) on the values of temperature and date. Your query might look something like this:

with agg (day, min_c, avg_c, max_c ) as (
    select date_trunc('day',ts) as ts,
    min("Air Temperature (Actual) (Degrees C)") as "Daily Min Temp (F)",
    avg("Air Temperature (Actual) (Degrees C)"),
    max("Air Temperature (Actual) (Degrees C)")
    from solardb.weatherdata
    group by date_trunc('day',ts) 
    order by date_trunc('day',ts)
)
select 
  agg.day as ts,
  agg.min_c*9/5 + 32  as "Daily Min Temp (F)",
  data_min.ts as "Time of minimum",
  agg.avg_c*9/5 + 32 as "Daily Avg Temp (F)",
  agg.max_c*9/5 + 32 as "Daily Max Temp (F)",
  data_max.ts as "Time of maximum"
from agg 
join solardb.weatherdata data_min 
  on agg.min_c = data_min."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_min.ts)
join solardb.weatherdata data_max 
  on agg.max_c = data_max."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_max.ts)

The above is not tested. I used the CTE to make logic more obvious, but one could use a simple subselect too.

Answer 2

You can use aggregates with windows functions to get the result. For example (I omit Celsius to Fahrenheit conversion for the sake of simplicity and readability ) :

with qr1 as
 (
    select a.* from 
    (
        select 
        ts,
        date_trunc('day',ts) as dt,
        temperature,
        min(temperature) over (partition by date_trunc('day',ts) )as min_temp ,
        avg(temperature) over (partition by date_trunc('day',ts) )as av_temp ,
        max(temperature) over (partition by date_trunc('day',ts) )as max_temp ,
        row_number()over (partition by date_trunc('day',ts) order by ts asc) as min_temp_rn,
        row_number()over (partition by date_trunc('day',ts) order by ts desc) as max_temp_rn

        from solardb.weatherdata
    )a 
    where a.min_temp_rn =1 or a.max_temp_rn = 1 
)
select 
dt as ts,
min_temp,av_temp,max_temp,
max(case when temperature=min_temp  then ts end) as min_temp_timestamp,
max(case when temperature=max_temp  then ts end) as max_temp_timestamp

from qr1

group by dt
order by dt