MySQL 中两个日期时间之间减号运算符的行为是什么? [英] What is the behavior for the minus operator between two datetimes in MySQL?

问题描述

日期时间之间的差异是它们之间的秒数.这似乎只有在日期时间发生在同一小时内时才有效.

The difference between to datetimes is the number of seconds between them. This seems to work only if the datetimes occur in the same hour.

这是为什么?

mysql> update events set created_at = "2011-04-13 15:59:59", fulfilled_at ="2011-04-13 16:00:00" where id = 1;
mysql> select fulfilled_at - created_at, timediff(fulfilled_at, created_at) from events where id = 1;
+---------------------------+------------------------------------+
| fulfilled_at - created_at | timediff(fulfilled_at, created_at) |
+---------------------------+------------------------------------+
|               4041.000000 | 00:00:01                           |
+---------------------------+------------------------------------+

我知道我应该使用 timediff，但我只是好奇为什么我会看到这个或者它是否被记录在某个地方.

I know I should be using timediff, but I'm just curious why I'm seeing this or if it's documented somewhere.

推荐答案

MySQL 只是尽可能地将字符串转换为数字，以便对它们进行数学运算.在这种情况下，它只是去除所有非数字冒号、破折号和空格.

MySQL is just converting strings into numbers as best it can, so that it can do the mathematical operation on them. In this case, its just stripping out all of the non numerical colons, dashes and spaces.

试试这个:

SELECT (20110413155959 - 20110413160000) AS dates;

你的日期，没有所有阻止它们成为数字的东西 - 结果是 -4041

Your dates, without all the stuff that stops them being numbers - the result is -4041

这篇关于MySQL 中两个日期时间之间减号运算符的行为是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！