Resample on time series data

Question

I have a table with time series column in the millisecond, I want to resample the time series and apply mean on the group. How can I implement it in Postgres?

"Resample" means aggregate all time stamps within one second or one minute. All rows within one second or one minute form a group.

table structure

date    x    y    z

Answer 1

Use date_trunc() to truncate timestamps to a given unit of time, and GROUP BY that expression:

SELECT date_trunc('minute', date) AS date_truncated_to_minute
     , avg(x) AS avg_x
     , avg(y) AS avg_y
     , avg(z) AS avg_z
FROM   tbl
GROUP  BY 1;

Assuming your misleadingly named date column is actually of type timestamp or timestamptz.

Related answer with more details and links:

• PostgreSQL: running count of rows for a query 'by minute'