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
I want to understand that what happens when I do
TIMESTAMPDIFF(SECOND, x_time, y_time)
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
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