how to get the time diff from 1900-01-01T14:30:00.000Z format - mysql?

Question

i have 2 dates and need to find the diff btw them in sql.

city       time_out                 time_in
Paris   1900-01-01T14:30:00.000Z    1900-01-01T17:50:00.000Z

select datediff(time_out - time_in) from trip where city = 'Paris'

not working.

Answer 1

use TIMEDIFF .

In case of days you have to parse the days also

CREATE tABLE trip(city varchar(10),time_out varchar(24),time_in varchar(24))

INSERT INTO trip VALUES
('Paris',   '1900-01-01T14:30:00.000Z',    '1900-01-01T17:50:00.000Z')

SELECT TIMEDIFF(CAST(time_in AS DATETIME),CAST(time_out AS DATETIME)) TIMEdiffrence FROM trip WHERE city = 'Paris'

| TIMEdiffrence |
| :------------ |
| 03:20:00      |

db<>fiddle here