0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
koral
  • 2,807
  • 3
  • 37
  • 65

1 Answers1

1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you. Is it still optimal for use with different periods like 15minutes? I'll also need to take first and last record from each set. And max - to draw a candle chart. – koral Feb 02 '21 at 10:34
  • @koral: `date:trunc()` only covers basic time units. For 15-min intervals you need a different approach, depending on additional undisclosed information. Consider: https://stackoverflow.com/a/15577413/939860 or https://stackoverflow.com/a/12731753/939860 or start a new question will all defining info. – Erwin Brandstetter Feb 03 '21 at 03:35