Get the average between dates of type timestamp

Question

Get the average between dates of type timestamp

Navigation

#1 by (3 votes)
#2 by (0 votes)

4

I have 3 dates:

2016-08-17 12:29:01
2016-08-17 12:34:13
2016-08-17 12:39:26

And I would like to get the average time between them. If there are more than 60 seconds, then in minutes, and the same for hours.

postgresql

asked by anonymous 18.08.2016 / 19:50

2 answers

How to run a code without open page in PHP [duplicate] Search for relationship Laravel whereHas?

score 3 · Answer 1

A solution using window functions follows:

SELECT
    cadastro.data AS data,
    EXTRACT(EPOCH FROM cadastro.data) - lag(EXTRACT( EPOCH FROM cadastro.data)) OVER (order by cadastro.data) AS intervalo
FROM
    ( SELECT unnest( ARRAY[  '2016-08-17 12:29:01'::timestamp, '2016-08-17 12:34:13'::timestamp, '2016-08-17 12:39:26'::timestamp ] ) as data) as cadastro

Output:

'2016-08-17 12:29:01';<NULL>
'2016-08-17 12:34:13';312
'2016-08-17 12:39:26';313

Reference:

link

score 0 · Answer 2

Subtract the smallest date from the largest and divide by the row count - 1:

with cadastro (data) as ( values
    ('2016-08-17 12:29:01'::timestamp),
    ('2016-08-17 12:34:13'),
    ('2016-08-17 12:39:26')
)
select (max(data) - min(data)) / (count(*) - 1) as média
from cadastro
;
   média    
------------
 00:05:12.5

The result is of type intervalo