I tried the following SQL query.
select start_time, end_time, end_time-start_time from orders
This printed the following output.
start_time |end_time |end_time-start_time ... 2019-11-29 15:55:54 | 2019-12-01 15:59:00 | 72000346 ...
I have no idea what the result means. I know that we can use TIMESTAMPDIFF to calculate difference between two datetime. Does anyone know what the result means?
Advertisement
Answer
MySQL has an unnerving tendency to treat date/time values as numbers or strings, rather than as date/times. Often, this is not important because the values are readily converted to date/times.
But, for the values in the your question, the representations are:
2019-12-01 15:59:00 --> 20191201155900 2019-11-29 15:55:54 --> 20191129155554
The result you are getting is 20191201155900 - 20191129155554
.
This happens because -
is an operator on numbers, and numbers have a higher preference. It can also happen with +
, but that can be fixed by adding interval
to the expression.