Aggregate function with Date on Postgres

Question

I'm kind of rusty on my SQL, maybe you can help me out on this query.

I have these two tables for a tickets system (I'm omitting some fields):

table tickets

id - bigint
subject - text
user_id - bigint
closed - boolean
first_message - bigint

(foreign key, for next table's id)

last_message - bigint

(same as before)

table ticket_messages

creation_date

I need to query the closed tickets, and make an average of the time spent between the first message creation_date and the last message creation_date. This is what I've done so far:

SELECT t.id, t.subject, tm.creation_date
FROM tickets AS t
INNER JOIN ticket_messages AS tm
ON tm.id = t.first_message
OR tm.id = t.last_message
WHERE t.closed = true

I'm looking for some group by or aggregate function to get all the data from the table, and try to calculate the time spent between last and first, also trying to display the dates for the first and last message.

---

UPDATE I added an inner Join with the second table instead of "OR", now I get both dates, and I can find the sum from my application:

SELECT t.id, t.subject, tm.creation_date, tm2.creation_date
FROM tickets AS t
INNER JOIN ticket_messages AS tm
ON tm.id = t.first_message
INNER JOIN ticket_messages as tm2
ON tm2.id = t.last_message
WHERE t.closed = true

I think that did it...

Answer 1

Something like this should do for getting the nr of days elapsed. You might need to put this in a subquery to easily pull out more fields from 'tickets'.

SELECT t.id,AVG(tlast.creation_date - tfirst.creation_date)
  FROM tickets AS t
INNER JOIN ticket_messages AS tfirst
  ON tm.id = t.first_message
INNER JOIN ticket_messages AS tlast
  ON tm.id = t.last_message
WHERE t.closed = true
GROUP BY t.id

Which might lead to(not tested..) e.g.

select t.id,t.subject,sub.nr_days 
FROM (
  SELECT t.id,AVG(tlast.creation_date - tfirst.creation_date) as nr_days
    FROM tickets AS t
  INNER JOIN ticket_messages AS tfirst
    ON tm.id = t.first_message
  INNER JOIN ticket_messages AS tlast
    ON tm.id = t.last_message
  WHERE t.closed = true
  GROUP BY t.id ) AS sub
INNER JOIN tickets AS t
  ON sub.id = t.id;

Answer 2

You are trying to combine two queries into one and trying to get the data from three rows of data from two tables. Both need to be fixed.

First of all, you should not attempt to mix aggregate data (such as averages) with the details for single items - you need separate queries for that. You can do it, but the output is repetitious and therefore wasteful (all the single items in a group will have the same aggregate data).

Secondly, you need to find the first message and the last message for a given ticket. Hence, that query is:

SELECT t.id, t.subject, tm1.creation_date as start, tm2.creation_date as end,
       tm2.creation_date - tm1.creation_date as close_interval
  FROM            tickets AS t
       INNER JOIN ticket_messages AS tm1 ON t.last_message = tm1.id
       INNER JOIN ticket_messages AS tm2 ON t.last_message = tm2.id
 WHERE t.closed = true

This gives you three rows of data per result row - as required. The computed value should be an interval type - assuming that PostgreSQL actually has that type. (In Informix, the type would effectively be INTERVAL DAY(n) for a suitable n, such as 9.)

You can average those intervals, now. You can't average dates because dates cannot be added together and cannot be divided; averaging involves both summing and dividing. Intervals can be added and divided.