Sum Column of Integers Based on Timestamp in PostgreSQL

Question

I am using PostgreSQL version 8.1. I have a table as follows:

          datetime     |    usage 
-----------------------+----------
2015-12-16 02:01:45+00 |    71.615
2015-12-16 03:14:42+00 |    43.000
2015-12-16 01:51:43+00 |    25.111
2015-12-17 02:05:26+00 |    94.087

I would like to add the integer values in the usage column based on the date in the datetime column.

Simply, I would like the output to look as below:

          datetime     |    usage 
-----------------------+----------
2015-12-16             |    139.726
2015-12-17             |    94.087

I have tried SELECT dateTime::DATE, usage, SUM(usage) FROM tableName GROUP BY dateTime::DATE, lngusage; which does not perform as expected. Any assistance would be appreciated. Thanks in advance.

DATE_TRUNC('day', datetime) according to the docs

Ed Gibbs
– Ed Gibbs

2016-03-04 21:38:24 +00:00
Commented Mar 4, 2016 at 21:38 — Ed Gibbs
– Ed Gibbs, Commented Mar 4, 2016 at 21:38

Darshan Mehta · Accepted Answer · 2016-03-04 21:33:18Z

2

Below query should give you the desired result:

select to_char(timestamp, 'YYYY-MM-DD') as time, sum(usage)
from table
group by time

answered Mar 4, 2016 at 21:33

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

maraca · Accepted Answer · 2016-03-04 21:33:29Z

0

This one is for postgreSQL, I see you added MySQL also.

SELECT
  dt
  SUM(usage),
FROM (
  SELECT
    DATE_TRUNC('day', datetime) dt,
    usage
  FROM
    tableName
) t
GROUP BY
  dt

answered Mar 4, 2016 at 21:33

maraca

9,5873 gold badges27 silver badges50 bronze badges

Comments

Jay T. · Accepted Answer · 2016-03-04 21:35:17Z

0

SELECT to_char(datetime, 'format'), sum(usage)
FROM table
group by to_char(datetime, 'format')

answered Mar 4, 2016 at 21:35

Jay T.

3071 silver badge6 bronze badges

Comments

viejoEngineer · Accepted Answer · 2016-03-04 22:02:53Z

0

In addition you could a window function.

SELECT DATETIME
,SUM(USAGE) OVER(PARTITION BY CAST(datetime AS DATE) ORDER BY datetime) AS Usage
FROM TableName

answered Mar 4, 2016 at 22:02

viejoEngineer

4044 silver badges14 bronze badges

Collectives™ on Stack Overflow

Sum Column of Integers Based on Timestamp in PostgreSQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related