Skip to content
Advertisement

What is the result of DateTime operation in MySQL

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement