Create a new column in MySQl with the difference between 2 dates in days from other columns

Question

I have to create a column that shows the duration between the date of a booking to a hotel and either the date of cancellation or the date of entrance.

I tried many things, but I'm getting errors constantly. I believe that it has to do with the datatype of the resulting column (I tried INT, TIME, DATE and TIMESTAMP).

ALTER TABLE reservations.hotel_res
ADD duration_res TIME;
INSERT INTO reservations.hotel_res (duration_res)
SELECT CASE WHEN date_cancellation IS NULL THEN TIMESTAMPDIFF(day,date_booking,date_entrance)
ELSE TIMESTAMPDIFF(day,date_booking,date_cancellation)
END FROM reservations.hotel_res;

Answer 1

You can add a virtual column to the table (details: see the documentation).

Example: DDL code (MySQL 5.7)

create table test (
  dbooking date
, dentrance date
, dcancellation date
, diff_ int as ( 
    case 
      when 
        dbooking is not null 
        and dentrance is not null 
        and dcancellation is null 
      then timestampdiff( day, dbooking, dentrance ) 
      when dbooking is not null 
        and dentrance is null 
        and dcancellation is not null 
      then  timestampdiff( day, dbooking, dcancellation ) 
      else 0
    end
  ) virtual
);

INSERTs

insert into test ( dbooking, dentrance ) values ( '2022-01-31', '2022-02-02' ) ;
insert into test ( dbooking, dcancellation ) values ( '2022-01-15', '2022-05-05' ) ;
insert into test ( dbooking ) values ( '2021-12-15' ) ;

Query

select * from test ;

-- result
dbooking    dentrance   dcancellation   diff_
2022-01-31  2022-02-02                  2
2022-01-15              2022-05-05      110
2021-12-15                              0

If you just want to add the virtual column without re-creating the table, use ...

alter table test2
add (
  diff_ int as ( 
    case 
      when 
        dbooking is not null 
        and dentrance is not null 
        and dcancellation is null 
      then timestampdiff( day, dbooking, dentrance ) 
      when dbooking is not null 
        and dentrance is null 
        and dcancellation is not null 
      then  timestampdiff( day, dbooking, dcancellation ) 
      else 0
    end
  ) virtual
) ;

DBfiddle here.