Build a table with start and end dates of period

I've been giving the idea some thought, and I think I've found the solution: Use user variables:

• The first thing I did was to order by idascending, and by fechadescending , to be able to "drag" the value of fecha.
• Next, I used a little trick with temporary variables: Since when assigning values ​​to user variables in a query they are updated on each row , then it is possible to reference their previous value before updating them :
  • An expression casechecks if the idcurrent record is the same as the idprevious record (which is in the variable @id_ant):
    • If the value of idis equal to the value of @id_ant, then put the value of @f_ini(which is updated on each row with the value of fecha);
    • If the value of is id not the same as the value of @id_ant, then put a NULL.
  • Then, update the values ​​of @id_antand @f_ini, so you can use them in the next row.

The result looked like this (I used [SQL Fiddle][ http://sqlfiddle.com/#!9/e4522] to make the problem reproducible; I added a few more values ​​to make the example clearer):

SQL-Fiddle

MySQL 5.6 Schema Setup :

create table prueba (
    id int unsigned not null,
    fecha datetime
);

insert into prueba values 
(10, '2016-12-01 10:15:00'),
(10, '2016-12-03 16:02:00'),
(10, '2016-12-05 19:19:00'),
(11, '2016-12-02 11:23:00'),
(11, '2016-12-04 22:00:00'),
(12, '2016-12-05 02:00:00');

Query 1 :

-- Contenido original de la tabla:
select * from prueba;

Results :

| id |               fecha |
|----|---------------------|
| 10 | 2016-12-01 10:15:00 |
| 10 | 2016-12-03 16:02:00 |
| 10 | 2016-12-05 19:19:00 |
| 11 | 2016-12-02 11:23:00 |
| 11 | 2016-12-04 22:00:00 |
| 12 | 2016-12-05 02:00:00 |

Query 2 :

-- Consulta original:
select case when @id_ant = id then @f_ini else null end as f_fin
     -- Actualiza el Id y la fecha de inicio
     , @id_ant := id as id
     , @f_ini := fecha as f_ini
from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
order by id asc, fecha desc;

Results :

|               f_fin | id |               f_ini |
|---------------------|----|---------------------|
|              (null) | 10 | 2016-12-05 19:19:00 |
| 2016-12-05 19:19:00 | 10 | 2016-12-03 16:02:00 |
| 2016-12-03 16:02:00 | 10 | 2016-12-01 10:15:00 |
|              (null) | 11 | 2016-12-04 22:00:00 |
| 2016-12-04 22:00:00 | 11 | 2016-12-02 11:23:00 |
|              (null) | 12 | 2016-12-05 02:00:00 |

Query 3 :

-- Consulta con los campos y los valores en el orden correcto:
select id, f_ini, f_fin
from (
        select case when @id_ant = id then @f_ini else null end as f_fin
             -- Actualiza el Id y la fecha de inicio
             , @id_ant := id as id
             , @f_ini := fecha as f_ini
        from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
        order by id asc, fecha desc
    ) as a
order by id, f_ini

Results :

| id |               f_ini |               f_fin |
|----|---------------------|---------------------|
| 10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00 |
| 10 | 2016-12-03 16:02:00 | 2016-12-05 19:19:00 |
| 10 | 2016-12-05 19:19:00 |              (null) |
| 11 | 2016-12-02 11:23:00 | 2016-12-04 22:00:00 |
| 11 | 2016-12-04 22:00:00 |              (null) |
| 12 | 2016-12-05 02:00:00 |              (null) |

It can also be achieved using MIN()in a sub-query:

select t.id,
       t.fecha_inicio,
       (select min(t2.fecha_inicio)
          from tbl t2
         where t2.id = t.id
           and t2.fecha_inicio > t.fecha_inicio) as fecha_final
  from tbl t
 order by t.id, t.fecha_inicio;

Demo

Demo 2