Timestamp difference between rows in Postgresql

Question

In PostgreSQL I have a table

CREATE TABLE cubenotification.newnotification
(
  idnewnotification serial NOT NULL,
  contratto text,
  idlocation numeric,
  typology text,
  idpost text,
  iduser text,
  idobject text,
  idwhere text,
  status text DEFAULT 'valid'::text,
  data_crea timestamp with time zone DEFAULT now(),
  username text,
  usersocial text,
  url text,
  apikey text,
  CONSTRAINT newnotification_pkey PRIMARY KEY (idnewnotification )
)

Let's say that typology field can be "owned_task" or "fwd_task". What I'd like to get from DB is the timestamp difference in seconds strictly between data_crea of the row with typology "fwd_task" and data_crea of the row with typology "owned_task" for every couple "idobject,iduser", and I'd like to get also the EXTRACT(WEEK FROM data_crea)) as "weeks", grouping the results by "weeks". My problem is principally about performing the timestamp ordered difference between two rows with same idobject, same iduser and different typology.

EDIT: Here some sample data

sample data

and sqlfiddle link http://sqlfiddle.com/#!12/6cd64/2

Some sample data would be very helpful. Possibly as sqlfiddle — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 24, 2013 at 15:20
sorry, now there are sample data. Building the example in sqlfiddle I made mistake about IDNEWNOTIFICATION values, they should have been inserted from 16 to 1 other than from 1 to 16 — Valerio Marcellino
– Valerio Marcellino, Commented Jan 24, 2013 at 16:01

Erwin Brandstetter · Accepted Answer · 2013-01-24 17:06:05Z

2

What you are looking for is the JOIN of two subselects:

SELECT EXTRACT(WEEK FROM o.data_crea) AS owned_week
     , iduser, idobject
     , EXTRACT('epoch' FROM (o.data_crea - f.data_crea)) AS diff_in_sek
FROM  (SELECT * FROM newnotification WHERE typology = 'owned_task') o
JOIN  (SELECT * FROM newnotification WHERE typology = 'fwd_task')   f
                                                         USING (iduser, idobject)
ORDER  BY 1,4

->sqlfiddle

I order by week and timestamp difference. The week number is based on the week of 'owned_task'.

This assumes there is exactly one row for 'owned_task' and one for 'fwd_task' per (iduser, idobject), not one per week. Your specification leaves room for interpretation.

answered Jan 24, 2013 at 17:06

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Timestamp difference between rows in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related