Postgresql – Postgres – using aggregate functions to return a different column

aggregatepostgresqltimestamp

I currently have a table containing raw temperature data that looks like this: enter image description here

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?

Best Answer

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.

Related Question