我可以将MysQL时间戳记与datetime列进行比较吗？是坏吗 [英] Can I compare MysQL timestamp with datetime columns? is it bad?

问题描述

所以，我有一个表ABC是时间戳，BCD是datetime。

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

如果我这样做：

SELECT * FROM myTable WHERE ABC > BCD

是不是坏？它会影响性能吗？

is it bad? and will it affect performance?

他们如何在性能方面进行比较？

How do they compare in terms of performance?

推荐答案

是的，您可以将 datetime 与时间戳进行比较。这不错，但要注意以下几点：

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

记住，虽然 DATETIME ， DATE 和 TIMESTAMP 值都可以使用相同的一组格式指定，类型并不都具有相同范围的价值观。例如， TIMESTAMP 值不得早于 1970 UTC 或晚于'2038-01 -19 03:14:07' UTC。

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.

这意味着日期如'1968-01-01'，而合法为 DATETIME 或 DATE 的值，作为 TIMESTAMP 值无效，转换为0。

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.

从 MySQL参考手册:: DATETIME，DATE和TIMESTAMP类型。

请注意以下测试用例一切工作正常：

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)

这篇关于我可以将MysQL时间戳记与datetime列进行比较吗？是坏吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！