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.
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.
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.
The last minus the first divided by the number of dates is equal to the average time between them. Right?
– Reginaldo Rigo
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
and2016-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– Jonathan
You want to do it in a specific language or just using sql?
– viana
Postgresql @Cleidimarviana
– Jonathan
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.
– William Aparecido Brandino
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.
– Reginaldo Rigo
This link can help you a little.
– viana