CODEWITHSUNDEEP
postgresql
Viktor SettleMint
Viktor SettleMint

Reputation: 37

query group aggregate value per 24h

My data looks like the following:

amount(double precision) time (timestamp without timezone)
3.53456532          "2021-03-29 09:41:09.052+00"
3.77389602          "2021-03-28 23:42:15.413+00"
3.77389602          "2021-03-28 23:42:10.176+00"
3.77389602          "2021-03-28 23:42:02.589+00"
3.77389602          "2021-03-28 23:41:57.226+00"
3.05223612          "2021-03-28 20:12:51.457+00"
21.55               "2021-03-28 18:50:35.174+00"
7.98374607          "2021-03-28 09:30:31.698+00"

What I would like to achieve is a select which would return me the following:

amount(double precision) time (timestamp without timezone)
3.53456532          "2021-03-29 00:00:00.000+00"
47.68156627         "2021-03-28 00:00:00.000+00"

So the total amount per 24h, I tried the following:

SELECT time as "time", 
  amount
FROM trades 
GROUP BY  DAY(time)

But I have then following issue:

pq: function day(timestamp with time zone) does not exist

I tried many alternatives but I am a bit stuck, any help? thanks!

Upvotes: 0

Views: 77

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

There is no day() function in PostgreSQL. Simply cast the time column to a date and aggregate on that.

select time::date as time,
       sum(amount) as amount
  from trades
 group by time::date

Please note: The values for 03/28 in your example sums to 47.68156627 instead of 45.91297.

Upvotes: 1

Related Questions