0

I have this:

SELECT 
  journals.id,
  issues.created_on,
  user_id,
  journalized_id,
  journals.created_on
FROM journals, issues 
where issues.id=journals.journalized_id and journalized_id=23363

It shows next result:

 id    user_id  journalized_id       created_on  
-----  -------  --------------  -------------------
77996      182           23363  2013-10-07 13:41:45
77998      182           23363  2013-10-07 13:43:08
78000      174           23363  2013-10-07 13:54:26
78137      174           23363  2013-10-08 15:38:43
78269      167           23363  2013-10-09 17:14:26
78328      182           23363  2013-10-10 12:35:02
78442      167           23363  2013-10-11 18:06:13

I want to get the time difference between one "created_on" record and the previous one or the next for the same journalized_id.

For ex: First record on the new row would be 0 ("2013-10-07 13:41:45"-"2013-10-07 13:41:45"), but next would be ("2013-10-07 13:43:08"-"2013-10-07 13:41:45"), I don't mind minutes or hours, and so on for the next, can you help me? How can I do it?

Thank you

pilcrow
  • 56,591
  • 13
  • 94
  • 135
Newbie
  • 1
  • possible duplicate of [How do I lag columns in MySQL?](http://stackoverflow.com/questions/5483319/how-do-i-lag-columns-in-mysql) and other ["How do I simulate LEAD() and LAG() in MySQL"](http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql) questions. – pilcrow Oct 20 '13 at 13:35

1 Answers1

0

I've no corresponding tables, but this syntax has been checked:

UPDATED use previous date & MySQL::TIMEDIFF():

SELECT journals.id, user_id, journalized_id
  , TIMEDIFF(journals.created_on,
    IFNULL(
      (SELECT MAX(t.created_on)
       FROM journals t
       INNER JOIN issues ON t.journalized_id=issues.id
       WHERE t.journalized_id=23363 AND t.id<journals.id),
    journals.created_on)
  )
FROM journals
INNER JOIN issues ON journals.journalized_id=issues.id
WHERE journalized_id=23363;

This may speed up using a variable @dprev = previous date:

SET @dprev := 0;
SELECT journals.id, user_id, journalized_id
  , TIMEDIFF(journals.created_on,
    IF(@dprev, @dprev, journals.created_on))
  , @dprev := journals.created_on
FROM journals
INNER JOIN issues ON journals.journalized_id=issues.id
WHERE journalized_id=23363;

? The inconvenient is that a third column @dprev is also shown.

jacouh
  • 8,473
  • 5
  • 32
  • 43
  • Hello Jacouh, thank you for your quick reply, I see that this row: journals.created_on - (SELECT MIN(journals.created_on) FROM journals INNER JOIN issues ON journals.journalized_id=issues.id WHERE journalized_id=23363) Creates this: 20131007132132.000000 (first row) 20131007132295.000000 (second row) 20131007133413.000000 (third row) 20131008151830.000000 How can I translate it to hours or minutes? Thank you. PS: Sorry but I can't get this text formatted properly :( – Newbie Oct 20 '13 at 12:00
  • Hello again, It doesn't work properly :( It's always using the lowest record, 2013-10-07 13:41:45 Any suggestion? Thank you – Newbie Oct 20 '13 at 13:44
  • I've missed the understanding, it's more difficult for vicinity datediffs. – jacouh Oct 20 '13 at 13:52
  • The problem is that is always using the first date, I suppose that I shall create some temporary value to store the previous? Not sure of that – Newbie Oct 20 '13 at 14:21
  • Yes this would be a solution. – jacouh Oct 20 '13 at 14:32
  • I only get 0 and 1, so I don't think so. – Newbie Oct 20 '13 at 15:09
  • 0 and 1 appear to be reasonnable 1st step, as deltas in days ? – jacouh Oct 20 '13 at 15:14
  • Yes! Now it works :) How can I thank you?? I can't vote, I'm new on this site... – Newbie Oct 20 '13 at 15:55
  • You can accept or not an answer as a solution - the biggest reward. – jacouh Oct 20 '13 at 16:09
  • If I could...I'd already, I've been thinking to make it more difficult, as I need to know how many time any issue is under someone hand after the creation, do you mind if I have a deeper look? Thank you! – Newbie Oct 22 '13 at 15:33