title: Calculating datetime between rows
body: <p>I have trouble calculating the time between lines. Though I was able to write an SQL that gives the correct time.</p>
  <pre><code>CREATE TABLE tab
      (`id` int,`station` varchar(10), `user` int, `create_at` datetime, `shift_end` datetime);
      
  INSERT INTO tab
      (`id`, `create_at`, `user`, `station`, `shift_end`)
  VALUES
      ( 1 , '2020-12-10 14:14:04',1,'S1', '2020-12-10 16:00:00'),
      ( 2 , '2020-12-10 14:14:09',1,'S2', '2020-12-10 16:00:00'),
      ( 3 , '2020-12-10 14:14:05',2,'S1', '2020-12-10 16:00:00'),
      ( 4 , '2020-12-10 14:14:15',2,'S2', '2020-12-10 16:00:00'),
      ( 5 , '2020-12-10 14:28:52',1,'S3', '2020-12-10 16:00:00');
  
  SELECT
    id,
    create_at,
    user,
    station,
    shift_end,
    TIMESTAMPDIFF(SECOND,
      (SELECT MAX(create_at) FROM tab WHERE create_at &lt; t.create_at and user = t.user),
      create_at
    ) AS x
  FROM tab as t order by user, create_at
  </code></pre>
  <p>But time appears in the next line. And here I have a question, is it even possible to obtain such a result?</p>
  <pre><code>id  create_at           user   station  shift_end            x
  1   2020-12-10 14:14:04 1      S1       2020-12-10 16:00:00  5
  2   2020-12-10 14:14:09 1      S2       2020-12-10 16:00:00  883
  5   2020-12-10 14:28:52 1      S3       2020-12-10 16:00:00  NULL
  3   2020-12-10 14:14:05 2      S1       2020-12-10 16:00:00  10
  4   2020-12-10 14:14:15 2      S2       2020-12-10 16:00:00  NULL
  </code></pre>
  <p>Is there something else, is it possible to calculate the time to the value in the shift_date column in the last rows for a given user?</p>
  <pre><code>id  create_at            user   station  shift_end               x
  5   2020-12-10 14:28:52  1      S3       2020-12-10 16:00:00     5468 (time between create_at AND shift_end)
  </code></pre>