[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] How to calculate the mean value of time intervals?
From:       Åsmund_Tokheim <asmundto () gmail ! com>
Date:       2014-12-28 19:51:56
Message-ID: CAFDjuaAmW52hGR3bx4L6aEqQMwiVYN=hTFLOnc44tf4PPMKYug () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]


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.

=C3=85smund

On Sun, Dec 28, 2014 at 1:58 PM, Tom van Tilburg <tom.van.tilburg@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 ar=
e
> 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@lists.osgeo.orghttp://lists.osgeo=
.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>

[Attachment #5 (text/html)]

<div dir="ltr">Hi Oliver<div><br></div><div>You have the AVG aggregate function that \
you normally should use to calculate the mean of a column. According to  <a \
href="http://www.postgresql.org/docs/8.2/static/functions-aggregate.html">http://www.postgresql.org/docs/8.2/static/functions-aggregate.html</a> \
it doesn&#39;t handle timestamps, but as far as I know, something like  \
</div><div>SELECT TO_TIMESTAMP(AVG( EXTRACT(EPOCH FROM &quot;timestamp&quot;) )) \
should give a correct answer.</div><div><br></div><div>Åsmund</div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Sun, Dec 28, 2014 at 1:58 PM, Tom \
van Tilburg <span dir="ltr">&lt;<a href="mailto:tom.van.tilburg@gmail.com" \
target="_blank">tom.van.tilburg@gmail.com</a>&gt;</span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">  
    
  
  <div bgcolor="#FFFFFF" text="#000000">
    <div>Hi Oliver,<br>
      <br>
      I think you question is more for the postgres user list, since it
      hasn&#39;t got anything to do with postgis.<br>
      <br>
      Nevertheless, you can look into window functions of postgres where
      you are able to get the value of the &#39;next&#39; row, based on a
      sorting with &#39;lead(rowname,1)&#39;. Once you calculated the interval
      between every row you can store that in a new column and do stats
      on that column.<br>
      <br>
<a href="http://www.postgresql.org/docs/current/static/functions-window.html" \
target="_blank">http://www.postgresql.org/docs/current/static/functions-window.html</a><br>
  <br>
      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.<br>
      <br>
      Best, Tom<div><div class="h5"><br>
      <br>
      <br>
      On 28-12-2014 10:41, Oliver Burgfeld wrote:<br>
    </div></div></div>
    <blockquote type="cite"><div><div class="h5">
      <div dir="ltr">Hi,<br>
        <br>
        I have a table with millions of rows including one column
        &#39;timestamp&#39;. Is it possible to calculate the mean time interval
        of all those entries?<br>
        I do know how to do it theoretically but don&#39;t really know how
        to convert it to SQL.<br>
        <br>
        Thanks!<br>
      </div>
      <br>
      <fieldset></fieldset>
      <br>
      </div></div><pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
  </blockquote>
    <br>
  </div>

<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>




_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread]