[postgis-users] Rounding up timestamps in postgresql

[Mark Cave-Ayland]

On 21/12/12 13:10, tasneem dewaswala wrote:

> Hello,
>
> I have a table consisting of following columns and data
>
> *Msg_count     Latitude         Longitude             Utc_Time*
> 16              56.6650356667  12.8772211667         142640
> 24              56.6650415       12.8772395               142640.25
> 35              56.6650475       12.8772581667          142640.5
> 26              56.665053         12.8772778333          142640.75
> 23              56.6650593333  12.8772976667          142641
> 26              56.6650656667  12.8773183333          142641.25
> 25              56.665072         12.8773388333          142641.5
> 15              56.6650785       12.8773598333           142641.75
>
> i need to find out sum of messages transmitted every second.
> For eg. when i group (142640, 142640.25, 142640.5, 142640.75) it makes 1
> sec and total number of messages sent during this interval are
> 16+24+35+26=101
>
> I don't know how can do this using a query.
>
> Does anyone have suggestion
>
> Regards..
> Tasha

Depending upon the field types in your query, you could try something 
like the following:

temp=# create table foo (
msg_count int,
latitude double precision,
longitude double precision,
utc_time double precision
);
CREATE TABLE
...
temp=# select sum(msg_count) AS total, trunc(utc_time) AS utc_time from 
foo group by trunc(utc_time) order by trunc(utc_time);
  total | utc_time
-------+----------
    101 |   142640
     89 |   142641
(2 rows)


ATB,

Mark.