I have a table in my postgresql 8.4 database like this:
id(serial), event_type_id(id, foreign key), ts(timestamp)
I have two event type id's that I need to see the age between.
Event_type_id 1 = Arrive
Event_type_id 2 = Leaves
example data
id | event_type_id | ts
----+---------------+----------------------------
21 | 6 | 2012-04-03 16:02:18.739274
20 | 5 | 2012-04-03 08:44:13.244287
I want to group these by day and see how many hours per day it is.
can someone point me in the direction how to group and calculate the age?
So far I have this, but I need to generate a series to join on or something similar
Solution Don't know if it's best practice but it works for me.
SELECT dx, age(max(ts), min(ts))
from generate_series('2012-04-01', '2012-04-14', interval '1 day') as dx
left join events on (ts >= dx and ts < dx + '23 hours 59 minutes' and event_type_id in (5,6))
group by dx
order by dx;
id=20
"belongs" to id=21
? What if you have more than two rows in that table - a_horse_with_no_name 2012-04-04 08:55
... JOIN events ON ts >= dx and ts < dx + interval '1d' AND ...
Erwin Brandstetter 2012-04-04 15:27
I think you need:
select age(max(ts), min(ts)), ts::date as date
from events
where event_type_id in (5,6)
group by date;
But your question is not clear. I assume you mean that you have a table (not a database) like this:
create table events (
id serial,
event_type_id int references event_types,
ts timestamp
);
And you need to calculate an interval between first and last event of type 5 or 6 for every date.
id
the primary key for the table? Can you show us some sample data - a_horse_with_no_name 2012-04-04 07:41