Aggregating to hourly data

Question

I have one time column structured like this

02/01/2003 07:01:42
02/01/2003 07:04:02
02/01/2003 07:36:11
02/01/2003 07:53:09

and an adjacent column with values as integers

2
4
1
1

I want to get the aggregate of the values (average) in hourly intervals. So:

02/01/2003 07

with "2" adjacent to hour 7 (2 is the average of 2,4,1,1)

Please provide enough code so others can better understand or reproduce the problem. — Community, Aug 01 '22 at 08:39

score 0 · Answer 1 · answered Aug 01 '22 at 00:54

CREATE TABLE test_data (
    test_date  TIMESTAMP NOT NULL,
    test_value INT NOT NULL
);

INSERT INTO test_data VALUES
  ('2003-02-01 07:01:42', 2),
  ('2003-02-01 07:04:02', 4),
  ('2003-02-01 07:36:11', 1),
  ('2003-02-01 07:53:09', 1),
  ('2003-02-01 08:02:16', 5);

SELECT extract(YEAR FROM test_date)  t_year,
       extract(MONTH FROM test_date) t_month,
       extract(DAY FROM test_date)   t_day,
       extract(HOUR FROM test_date)  t_hour,
       avg(test_value)               t_avg
  FROM test_data
GROUP BY extract(YEAR FROM test_date),
         extract(MONTH FROM test_date),
         extract(DAY FROM test_date),
         extract(HOUR FROM test_date);

will result in

t_year	t_month	t_day	t_hour	t_avg
2003	2	1	7	2.0000
2003	2	1	8	5.0000

Aggregating to hourly data

1 Answers1