postgresql - How can I average table rows by timestamp? -

i have table in postgresql created this:

-- table: mytable -- drop table mytable; create table mytable (   "timestamp" double precision,   "temperature" double precision,   "pressure" double precision,   "milligrams" double precision,   table_pkey serial not null,   constraint mytable_pkey primary key (table_pkey) ); 

where timestamp number of elapsed seconds since particular day.

i see how average number of table rows produce condensed table. example,

select avg("timestamp") "seconds", avg("temperature") "temperature",        avg("pressure") "pressure", avg("milligrams") "milligrams" (select row_number() over(order null) - 1 rn, * mytable) t  group (rn/120)  order rn/120 

would output table condensed factor of 120 input rows per output row.

instead of averaging number of rows together, want average spans of timestamp together. produce, example, table each row contains average values on each hour of day.

edit

this, combined changing timestamp column type typestamp seems work:

select date_trunc('hour', "acquired"), avg("temperature"),  avg("pressure"), avg("milligrams") mytable 1=1 group date_trunc('hour', "acquired") order 1 

i change "timestamp" column type timestamp type (and maybe change column name "created" avoid confusion), this:

create table mytable (   "created" timestamp,  -- changed column definition   "temperature" double precision,   "pressure" double precision,   "milligrams" double precision,   ... ) 

so, produce, example, table each row contains average values on each hour of day, can use date_part , group accordingly:

select date_part('hour', created), avg("temperature") mytable ... group date_part('hour', created)