Average between timestamp dates

Asked

Viewed 758 times

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

  • The last minus the first divided by the number of dates is equal to the average time between them. Right?

  • I would like to know the average time of the dates. Then it would be add the interval from one date to another, and divide. For example, 2016-08-17 12:39:26 - 2016-08-17 12:34:13 = 00:05:14 and 2016-08-17 12:34:13 - 2016-08-17 12:29:01 = 00:05:12. Therefore (00:05:14 + 00:05:12) / qdteDatas - 1. I think it’s the same, but the variable is qdteDatas, instead of using 2 dates (first and last) @Reginaldorigo

  • You want to do it in a specific language or just using sql?

  • Postgresql @Cleidimarviana

  • I believe that if you have something to cast in milliseconds, do the math average operation, and return the cast to date. If you want, later I record as an answer to see if it works.

  • That’s right. The sum of the time between them is equal to the difference of the time between the first and the last. So there is no mystery.

  • This link can help you a little.

Show 2 more comments

2 answers

3

Follows a solution using window functions:

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

Exit:

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

Reference:

https://www.postgresql.org/docs/9.4/static/functions-window.html

  • Whereas dates are found in a table cadastro and a field called data, how would you look ?

  • @Jonathan: SELECT cad.data AS data, EXTRACT(EPOCH FROM cad.data) - lag(EXTRACT( EPOCH FROM cad.data)) OVER (order by cad.data) AS intervalo FROM cadastro AS c;

  • Dude, it didn’t work out here. These data refers to the field or alias ? @Lacobus

0

Subtract the lowest date from the highest and divided by line 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 like intervalo

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.