CODEWITHSUNDEEP
sqlpostgresqldatetimeaggregate-functions
koral
koral

Reputation: 2965

Select minimum value from each 1 hour block

I have data: timestamp, value.

CREATE TABLE cgl
(
    id integer NOT NULL DEFAULT nextval('cgl_id_seq'::regclass),
    ts integer,
    value integer
)

I want to have minimum value from each 1 hour. There is unknown number of records per hour. I know more or less (rather less) I should use partition. Please point me to some examples or directions.

Upvotes: 0

Views: 462

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

Assuming ts is supposed to be type timestamp:

SELECT date_trunc('hour', ts) AS hour, min(value) AS min_val
FROM   cgl
GROUP  BY 1;

date_trunc() truncates timestamps to the hour. Of course, you get no row for hours without any data from this. If you need a row for *every hour, generate a total set of hours with generate_series() first. Example:

If ts is in fact integer, it's probably supposed to be a UNIX epoch, and you need to convert it with to_timestamp() first:

SELECT date_trunc('hour', to_timestamp(ts)) AS hour, min(value) AS min_val
FROM   cgl
GROUP  BY 1;

Consider storing a proper timestamp to begin with.

Aside: "partition" would hint at window functions, which would be the wrong tool for this.

Upvotes: 1

Related Questions