CODEWITHSUNDEEP
sqlpostgresqldatedatetimeaverage
Deni Suryadi
Deni Suryadi

Reputation: 31

Postgresql Query - Average time from datetime field in Posgresql?

I have an SQL table:

Id | EntryTime | Duration

And I want to find the average entry TIME OF DAY between two hours, taking into account all records between those hours.

so if my EntryTime field between my hours:

(Edited)
12:00:00
10:00:00
08:00:00
10:00:00

Then the average time returned should just be:

10:00:00

The date should not be taken into account at all, and it should be returned in string format, or a manner which returns ONLY 10:00:00.

Upvotes: 3

Views: 1161

Answers (2)

Игорь Тыра
Игорь Тыра

Reputation: 955

Try SELECT avg(entry_time - entry_time::date)::time(0) to avoid seconds fraction.

Upvotes: 0

GMB
GMB

Reputation: 222402

If I follow you correctly, you can use intervals for this; the idea is to substract the date part from the timestamp: this gives you an interval that represents the time portion, than you can then average.

Assuming that your table is called t and that the timestamps are stored in column ts, that would be:

select avg(entry_time - entry_time::date) avg_time from t

Demo on DB Fiddle

Sample data:

| entry_time          |
| :------------------ |
| 2020-07-14 12:00:00 |
| 2020-07-12 10:00:00 |
| 2020-07-10 08:00:00 |
| 2020-07-01 10:00:00 |

Results:

| avg_time |
| :------- |
| 10:00:00 |

Upvotes: 1

Related Questions