1

I want to convert 15 min interval table in postgres database to hourly intervals and also sum up all the values in the other columns as per that. How do I do this? what would the query be?

example:

timestamp                    count
"2015-01-05 12:00:00"          35
"2015-01-05 12:15:00"       45
"2015-01-05 12:30:00"       23
"2015-01-05 12:45:00"       23
"2015-01-05 01:00:00"       45
"2015-01-05 01:15:00"       12
"2015-01-05 01:30:00"       11
"2015-01-05 01:45:00"        56

I want the output table to be

timestamp                  count
2015-01-05 12:00:00         126
2015-01-05 01:00:00         124
swat
  • 21
  • 5

2 Answers2

3

Easy, in PostgreSQL:

SELECT date_trunc('hour', timestamp_col) as ts_hour, sum(count_col) 
FROM counts_table
GROUP BY ts_hour
ORDER BY ts_hour;
FuzzyChef
  • 3,879
  • 2
  • 17
  • 17
1

FuzzChef gave a good answer, but you might need to change the order by from an alias to the actual date_trunc as in:

SELECT date_trunc('hour', timestamp_col) as ts_hour, sum(count_col) FROM counts_table 
GROUP BY date_trunc('hour', timestamp_col) 
ORDER BY date_trunc('hour', timestamp_col) 
;

Also, if you just want the HOUR to show up, then use extract as in:

SELECT extract('hour' from timestamp_col) as ts_hour, sum(count_col) FROM counts_table 
GROUP BY extract('hour' from timestamp_col) 
ORDER BY extract('hour' from timestamp_col) 
;
Walker Farrow
  • 3,579
  • 7
  • 29
  • 51
  • 1
    References to output column names are allowed in `GROUP BY` and `ORDER BY` - and acutally take precedence over input column names. FuzzyChef's answer is better in this respect. [Details here.](http://stackoverflow.com/questions/11218257/postgresql-reusing-computation-result-in-select-query/11218453#11218453) Your second query would lump multiple days together, which is most probably not intended. – Erwin Brandstetter Feb 01 '15 at 03:50
  • Thank you so much. That was really helpful. If there is id that defines each of them, then I just add id too in the SELECT field right? – swat Feb 02 '15 at 16:07
  • Yes of course, and then you would need to do a group by on that - i.e. add it to the existing group by field – Walker Farrow Feb 02 '15 at 17:58