Grouping logs by ip and time

From: Alex Thurlow <alex-reports(at)blastro(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Grouping logs by ip and time
Date: 2011-11-08 17:59:51
Message-ID: 4EB96E17.9060804@blastro.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

The log type can be action1, action2, action3, action4, or action5. I
know that each user session will have a max of one of each log and it
will always start with action1. It may not have every action though. I
also know that each session will take no longer than one minute.

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.

I know how to script it, although it's very slow. I was hoping there
was some way to do this in SQL. I'm running Postgresql 8.3.7 on this
machine right now, but if there's something that would make this easier
and doesn't exist there yet, I would be willing to upgrade.

Thanks,
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2011-11-08 18:48:52 Re: Grouping logs by ip and time
Previous Message John R Pierce 2011-11-08 17:55:17 Re: Postgres vs other Postgres based MPP implementations