在mysql中从午夜减去时间

Question

I am using mysql and I have a column which tracks the datetime, in the form, yyyy-mm-dd hh:mm:ss. I want to fetch that timestamp and calculate the difference of midnight to the time.

Example, if the datetime has 2014-11-22 13:53:41, I want the value of: (Midnight of 2014-11-22) - (2014-11-22 13:53:41).

The value comes out to be: 11:07:19.

I want this value to be shown as counter on my website which uses php.

Answer 1

Use TIMESTAMPDIFF and SEC_TO_TIME

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND,  '2014-11-22 00:00:00',  '2014-11-22 13:53:41'))