How to calculate average of values with the same timestamp in Postgresql?

Question

i have the following table:

date	value	Average
2021-04-07 18:00:00	5
2021-04-07 18:00:00	10
2021-04-07 18:02:00	5
2021-04-07 18:02:00	4
2021-04-07 18:03:00	5
2021-04-07 18:03:00	8

And i want to know how could i calculate the average of values with the same timestamp, like this:

date	value	Average
2021-04-07 18:00:00	5	7,5
2021-04-07 18:00:00	10	7,5
2021-04-07 18:02:00	5	4,5
2021-04-07 18:02:00	4	4,5
2021-04-07 18:03:00	5	6,5
2021-04-07 18:03:00	8	6,5

I'm also wondering if it would be easier to calculate the average on a separate table so they won't repeat because there is more than one row with the same timestamp. Hope someone can help me with this, thanks in advance.

Answer 1

A simple query:

SELECT date, AVG(value) as avg_value FROM MyTable GROUP BY date

Answer 2

SELECT CAST(date AS DATE) as DateField, AVG(value) as avg_value
FROM MyTable
GROUP BY CAST(date AS DATE)

Answer 3

In an internal select, obtain the avg with the group by date . Then join the records whose date is equal to the internal select date.

SELECT t1.date,t2.value,t1.average 
FROM
 (SELECT date,AVG(Cast(value as Float)) as average
 FROM yourTable
 GROUP BY date) t1 JOIN yourTable t2 ON t1.date = t2.date

result: dbfiddle

Answer 4

Use a window function:

select t.*,
       avg(value) over (partition by date) as timestamp_average
from t;