Can I compare MysQL timestamp with datetime columns? is it bad?

Question

So, I have a table where columns "ABC" is a timestamp and "BCD" is a datetime.

If I do this:

SELECT * FROM myTable WHERE ABC > BCD

is it bad? and will it affect performance?

How do they compare in terms of performance?

Answer 1

Yes, you can compare a datetime with a timestamp. It's not bad, but be aware of the following:

Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 UTC or later than '2038-01-19 03:14:07' UTC.

This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0.

From MySQL Reference Manual :: The DATETIME, DATE, and TIMESTAMP Types.

Note how the following test cases all works fine:

CREATE TABLE t1 (d1 datetime, d2 timestamp);

INSERT INTO t1 VALUES ('1968-01-01 00:00:00', '1980-01-01 00:00:00');
INSERT INTO t1 VALUES ('2040-01-01 00:00:00', '1980-01-01 00:00:00');

SELECT * FROM t1 WHERE d2 < d1;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2040-01-01 00:00:00 | 1980-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

SELECT * FROM t1 WHERE d2 > d1;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 1968-01-01 00:00:00 | 1980-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

SELECT * FROM t1 WHERE d2 < '2040-01-01 00:00:00';
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 1968-01-01 00:00:00 | 1980-01-01 00:00:00 |
| 2040-01-01 00:00:00 | 1980-01-01 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

SELECT * FROM t1 WHERE d2 > '2040-01-01 00:00:00';
Empty set (0.00 sec)