CODEWITHSUNDEEP
sqlpostgresqltime-seriesaggregate-functions
Hello lad
Hello lad

Reputation: 18790

Resample on time series data

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

Upvotes: 4

Views: 11453

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

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:

Upvotes: 13

Related Questions