[postgis-users] Rounding up timestamps in postgresql

[Stephen Woodbridge]

select count(*) from table where round(utc_time)=142640;

or

select round(utc_time), sum(msg_count) as msg_count from table group by 
round(utc_time) order by msg_count desc;

-Steve

On 12/29/2012 5:19 PM, Mark Cave-Ayland wrote:
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users