[postgis-users] How to calculate the mean value of time intervals?

[Åsmund Tokheim]

Hi Oliver

You have the AVG aggregate function that you normally should use to
calculate the mean of a column. According to
http://www.postgresql.org/docs/8.2/static/functions-aggregate.html it
doesn't handle timestamps, but as far as I know, something like
SELECT TO_TIMESTAMP(AVG( EXTRACT(EPOCH FROM "timestamp") )) should give a
correct answer.

Åsmund

On Sun, Dec 28, 2014 at 1:58 PM, Tom van Tilburg <tom.van.tilburg at gmail.com>
wrote:

>  Hi Oliver,
>
> I think you question is more for the postgres user list, since it hasn't
> got anything to do with postgis.
>
> Nevertheless, you can look into window functions of postgres where you are
> able to get the value of the 'next' row, based on a sorting with
> 'lead(rowname,1)'. Once you calculated the interval between every row you
> can store that in a new column and do stats on that column.
>
> http://www.postgresql.org/docs/current/static/functions-window.html
>
> I have no idea what this does with speed on millions of records, but you
> better use an index on your timestamp for the sorting.
>
> Best, Tom
>
>
>
> On 28-12-2014 10:41, Oliver Burgfeld wrote:
>
> Hi,
>
> I have a table with millions of rows including one column 'timestamp'. Is
> it possible to calculate the mean time interval of all those entries?
> I do know how to do it theoretically but don't really know how to convert
> it to SQL.
>
> Thanks!
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141228/bb185b14/attachment.html>