get datetime difference of a specific time and value of date time column I have a datetime column in my mysql table.
I need to get the difference between a definite time everyday say 12.00 pm and the value of that column.
This was trickier than I expected (been a while since I've used the date and time functions) - so I did the following.

Created a table:

CREATE TABLE times (t timestamp);


Then put in some random data:

INSERT INTO times VALUES ('2016-06-27 01:01:12');
INSERT INTO times VALUES ('2016-06-27 07:34:17');
INSERT INTO times VALUES ('2016-06-27 14:56:03');
INSERT INTO times VALUES ('2016-06-27 23:24:56');


And then ran the best function that I could find:

SELECT TIMESTAMPDIFF(MINUTE, t, '2016-06-27 12:00:00') AS tdiff FROM times;


And obtained the result (correct - in minutes):

tdiff
-----
658
265
-176
-684


You can, of course, divide (or multiply) by whatever factor you choose to convert the outputs to the units of your choice.

Depending on your needs, these might suit you better:

- SELECT TIMEDIFF(t, '2016-06-27 12:00:00') AS tdiff FROM times;

- SELECT UNIX_TIMESTAMP(t) - UNIX_TIMESTAMP('2016-06-27 12:00:00') AS tdiff FROM times;

- SELECT TO_SECONDS(t) - TO_SECONDS('2016-06-27 12:00:00') AS tdiff FROM times;


Following @AndrewBrennan's answer below, I've expanded on it somewhat - i.e. if one wished to run the query over a period of a week/month/quarter - whatever. It will perform the relevant calculation comparing datetimes on a given day in the datetime field with the fixed definite time - i.e. comparing like with like on the particular day in question.

SELECT TIMESTAMPDIFF(MINUTE, 
                     t, 
                     CONCAT(YEAR(t), '-',  
                            MONTH(t), '-', 
                            DAY(t), 
                            ' 12:00:00'))
AS tdiff
FROM times;