Re: Grouping logs by ip and time - Mailing list pgsql-general

On 08/11/2011 17:59, Alex Thurlow wrote:
> Hello all,
>     I have a table which stores action logs from users.  It looks
> something like this:
> log_type text,
> date date,
> "time" time without time zone,
> ip inet

[snip]

> What I'd like to do is be able to group these logs by sessions based on
> the IP and the time range so I can figure out the time taken between
> each action.

Would something like this work? -

  select ip, max("time") - min("time") as session_duration
  from log_table
  group by ip;

This doesn't take the date into account - what happens if the session
spans midnight? You can get around this by using a timestamp column
instead of separate date and time.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie