When retrieving and displaying TIME data, MySQL uses the format ‘hh:mm:ss’ (or ‘hhh:mm:ss’ for huge hours values). ‘-838:59:59’ to ‘838:59:59’ are the possible TIME values. The TIME type can be used to indicate elapsed time or the duration between two occurrences, both of which may be much longer than 24 hours or even negative, in addition to a time of day (which must be less than 24 hours). This may explain why the hours element is so huge.
The following example demonstrates the usage of the TIME data type. Also this example will let you know how to get time difference between two date-times.
CREATE TABLE TempTimeTable(
BeginDateTime DATETIME,
EndDateTime DATETIME,
TimeDifference TIME);INSERT INTO TempTimeTable(BeginDateTime, EndDateTime) VALUES
(‘2023-05-17 12:11:00′,’2023-05-17 11:07:00’);
INSERT INTO TempTimeTable(BeginDateTime, EndDateTime) VALUES
(‘2023-05-17 09:52:00′,’2023-05-17 09:46:00’);
INSERT INTO TempTimeTable(BeginDateTime, EndDateTime) VALUES
(‘2023-05-17 09:23:21′,’2023-05-17 08:07:09’);UPDATE TempTimeTable SET TimeDifference = BeginDateTime-EndDateTime;
SELECT * FROM TempTimeTable;
The above statement returns the following output.
01:04:00
00:06:00
01:16:12
However, if the dates are different, the above specified calculation will not work. The time difference must be calculated using TIMEDIFF or TIMESTAMPDIFF function.
Big Data and SQL 