EverSQL Database Performance Knowledge Base
How can I average table rows by timestamp?
Database type:
I have a table in PostgreSQL that is created like 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 is some number of elapsed seconds since a particular day.
I see how to average some number of table rows together to produce a condensed table. For example,
SELECT AVG("TimeStamp") "Seconds", AVG("Temperature") "Temperature",
AVG("Pressure") "Pressure", AVG("Milligrams") "Milligrams"
FROM (SELECT ROW_NUMBER() OVER(ORDER BY null) - 1 rn, * FROM myTable) t
GROUP BY (rn/120)
ORDER BY rn/120
would output a table condensed by a factor of 120 input rows per output row.
Instead of averaging some number of rows together, I want to average some spans of TimeStamp together. To produce, for example, a table where each row contains the average values over each hour of the day.
EDIT
This, combined with changing the timestamp column to type typestamp seems to work:
SELECT date_trunc('hour', "Acquired"), AVG("Temperature"),
AVG("Pressure"), AVG("Milligrams")
FROM myTable
WHERE 1=1
GROUP BY date_trunc('hour', "Acquired")
ORDER BY 1