What happens when we take a timediff between timestamp and datetime

Question

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

Answer 1

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.

Answer 2

You are basically going to drive mysql crazy because of how the query gets evaluated.

Look at the query again

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

Please note what the query will do before present any data

• You will be doing a full table scan
• You are literally performing data subtraction on every row
• You will collect all rows that fulfill the query into a temp table
• You will skip 100,000 rows in the temp table
• You will retrieve 5,000 thereafter

Indexing both columns will not help. Why ? Because of the required date subtraction, which does not need indexes. This being the case, the internal conversion to one date type or another is the least of your worries.

SUGGESTION

Store the time difference and index it

ALTER TABLE test ADD timediffsec INT DEFAULT 0;
UPDATE test SET timediffsec = UNIX_TIMESTAMP(x_time) - UNIX_TIMESTAMP(y_time);
ALTER TABLE test ADD INDEX (timediffsec);

Now you can query like this:

SELECT * from test WHERE timediffsec > 0 LIMIT 100000, 5000

Checking time differences already calculated is better for performance than calculating it for every row.

Give it a Try !!!