Using age in postgresql - 【StackMirror】|sql|postgresql

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;

2012-04-04 07:32
by heldt

Is id the primary key for the table? Can you show us some sample data - a_horse_with_no_name 2012-04-04 07:41

I feel you have to rethink your schema.Will it be simple if you have the timestamp in the event table - sathis 2012-04-04 07:44

edit post with example data and my current sq - heldt 2012-04-04 07:47

How do you know that the row with 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

Sorry for bad data example. Updated it now with better data. I know that every day there is one event for eventTypeid 6 and 5. This is just a temporary table for me to check some data and not a application to be used so the design may have some flaws - heldt 2012-04-04 14:10

Upper limit in your solution is incorrect. Try: ... 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.

2012-04-04 08:44
by Tometzky