I am trying following query to insert data from a file. How do I calculate TOTAL_HOURS from in_date and out_date? I know we can not select them as "as in_date" and use it later. It was for illustration purpose only. The script is reading data from a file and it is not stored in database yet.

insert into Table_A (
    Id,
    IN_DATE,
    OUT_DATE,
    TOTAL_HOURS,
)
values (
    1,
    '2021-10-04 07:00:00' as in_date,
    '2021-10-04 07:00:00' as out_date,
    DATEDIFF(second, out_date, in_date) / 3600.0
);

CodePudding user response：

If you want to INSERT a static datetime using TIMESTAMPDIFF:

INSERT INTO Table_A (Id, IN_DATE, OUT_DATE, TOTAL_HOURS)
SELECT 1, '2021-10-04 07:00:00', '2021-10-04 08:00:00',
TIMESTAMPDIFF(HOUR, '2021-10-04 07:00:00', '2021-10-04 08:00:00')

If you want to INSERT a dynamic datetime using data from another table (i.e. Table_B to Table_A using a key):

INSERT INTO Table_A (Id, IN_DATE, OUT_DATE, TOTAL_HOURS)
SELECT Id, IN_DATE, OUT_DATE,
TIMESTAMPDIFF(HOUR, IN_DATE, OUT_DATE) FROM Table_B WHERE Id = 1

Result:

Fiddle here.

To INSERT from a .csv file use the LOAD DATA INFILE command and add the TIMESTAMPDIFF calculation to the SET clause:

LOAD DATA INFILE 'c:/tmp/discounts_2.csv'
INTO TABLE Table_A
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(Id,@IN_DATE,@OUT_DATE)
SET IN_DATE=@IN_DATE,
OUT_DATE=@OUT_DATE,
TIMESTAMPDIFF(HOUR, @IN_DATE, @OUT_DATE);