CODEWITHSUNDEEP
sqlpostgresqldate
swat
swat

Reputation: 21

Postgres query to convert the 15 minute interval to hourly interval

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

Upvotes: 1

Views: 1168

Answers (2)

FuzzyChef
FuzzyChef

Reputation: 4061

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;

Upvotes: 3

Walker Farrow
Walker Farrow

Reputation: 3875

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) 
;

Upvotes: 1

Related Questions