How to add a column that contains the next date column with SQL oracle developper

I want to add a column to my table that contains the next Birthdate (ordered by Birthdate)

This is my actuale table:

And I’m looking for this table

Is there any function like ‘NEXT(Birthdate)’ ? in order to calculate the difference in min:ss between the Birthdate column and the next_Birthday column

>Solution :

That would be LEAD analytic function.

Sample data:

SQL> with test (id, birthdate) as
  2    (select 'personne1', to_timestamp('19.11.1990 19:14:52,182000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual union all
  3     select 'personne1', to_timestamp('19.11.1990 20:00:52,170000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual union all
  4     select 'personne1', to_timestamp('19.11.1990 21:14:00,190000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual
  5    ),

As you want to calculate something based on those values, use extract function which works well on timestamps (which is what you, apparently, have):

  6  temp as
  7    (select id,
  8       birthdate,
  9       lead(birthdate) over (partition by id order by birthdate) nextdate
 10     from test
 11    )
 12  select id, birthdate, nextdate,
 13    extract(minute from (nextdate - birthdate)) mins,
 14    extract(second from (nextdate - birthdate)) secs
 15  from temp;

ID        BIRTHDATE                      NEXTDATE                             MINS       SECS
--------- ------------------------------ ------------------------------ ---------- ----------
personne1 19.11.90 19:14:52,182000000    19.11.90 20:00:52,170000000            45     59,988
personne1 19.11.90 20:00:52,170000000    19.11.90 21:14:00,190000000            13       8,02
personne1 19.11.90 21:14:00,190000000

SQL>