Mysql – What happens when we take a timediff between timestamp and datetime

datetimeMySQLmysql-5.5timestamp

Say I have following table in mysql (5.5+):

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `y_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My x_time is a timestamp, and y_time is a datetime column

I want to understand that what happens when I do TIMESTAMPDIFF(SECOND, x_time, y_time)

Does x_time gets converted to datetime and then the difference is calculated? or it happens vice-versa? Mysql manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff) says: One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary.

My assumption is that both the columns x_time and y_time has to been of one datatype to calculate TIMESTAMPDIFF, and if it is not already then mysql has to do it internally. Am I wrong here?

Also, if it has to convert one of the column to match the another column's datatype, then how the performance will matter for the following query? Will it do the datatype conversion for 5000 rows? And how time consuming process is this?

SELECT * from test WHERE `TIMESTAMPDIFF(SECOND, x_time, y_time)` LIMIT 100000, 5000

Best Answer

In addition to Rolando's answer, please note that a TIMESTAMP column is timezone-aware, while DATETIME is not.

If you don't have multiple time zones, then you're OK; but still, if ever you wanted to convert to other timezones, the TIMESTAMPDIFF calculation will provide with corrupted results.

Related Question