mysql - 比较两个unix时间戳以查看它们在MySql中是否处于同一日期

Question

我在 Stackoverflow 上阅读了有关 FROM_UNIXTIME 使用的各种问题，但没有一个直接涉及我想要做的事情。我的变量中有一个时间戳，该变量来自 php(已重新格式化 - 例如 2014 年 3 月 25 日)到一个函数，该函数使用数据库查询来确定数据库中是否存在具有相同日期(而不是时间)的其他条目。我已经使用 MySql 运行过各种格式化和比较时间戳条目的方法，并最终得到以下结果，但我知道它不是很有效。有谁知道更好的方法来实现这一目标？

FROM_UNIXTIME(sd.timestart, "%e %M %Y") = ?'

其中数组中用于比较的变量是上面列出的日期格式。这实现了我想要的，但我再次认为这不是完成此任务的最有效方法。任何建议和/或想法将不胜感激。

*编辑*我的时间戳存储为整数，因此我尝试使用:

$thissessiondate = strtotime($date->timestart, strtotime('today')); 

和

$tomorrowdate = strtotime($date->timestart, strtotime('tomorrow'));

要修剪到午夜，但出现错误(strtotime() 期望参数 2 很长)，当我将“今天”移动到第一个参数位置时，我得到的转换为晚上 11 点而不是 0:00。 .?我正在取得一些进展，但我对 PHP 和 MySQL 的了解非常不全面，这阻碍了我。

Answer 1

如果可以避免，请不要将谓词中使用的列包装在表达式中。

将谓词放在裸列上以使索引范围扫描成为可能。您希望尽可能在谓词的字面量方面进行数据类型转换。

STR_TO_DATE 函数对此最为方便。

假设 timestart 列是 DATE、DATETIME 或 TIMESTAMP(如果它代表一个时间点，它确实应该是这样。)

WHERE sd.timestart >= STR_TO_DATE( ? , "%e %M %Y") 
  AND sd.timestart <  STR_TO_DATE( ? , "%e %M %Y") + INTERVAL 1 DAY

实际上，它所做的就是将字符串作为第一个参数传入 STR_TO_DATE 函数，MySQL 将根据第二个参数指定的格式将该字符串转换为 DATETIME。这实际上成为 MySQL 可以用来与列中存储的值进行比较的文字。

如果有合适的索引可用，MySQL 将考虑索引范围扫描操作来满足该谓词。

您需要两次传入相同的值，但这并不是真正的问题。

在第二行，我们只是在同一值上添加一天。那么 MySQL 看到的是这样的:

WHERE sd.timestart >= STR_TO_DATE( '25 March 2014' , "%e %M %Y") 
  AND sd.timestart <  STR_TO_DATE( '25 March 2014' , "%e %M %Y") + INTERVAL 1 DAY

就性能而言，这相当于:

WHERE sd.timestart >= '2014-03-15 00:00:00'
  AND sd.timestart <  '2014-03-16 00:00:00'

如果您以相反的方式执行此操作，并将 timestart 包装在一个函数中，则将要求 MySQL 在每一行上(或者至少在没有被另一个谓词首先过滤掉的每一行上)计算该函数.)

重要说明

请注意，MySQL 将日期时间值解释为 MySQL 连接的时区，默认为 MySQL 服务器的时区设置。 MySQL 将在当前时区设置中解释日期时间文字。例如，如果 MySQL 时区设置为 +00:00，则日期时间文字将被解释为 UTC。

<小时/>

我假设格式字符串与传入的数据匹配，我不使用 %e 或 %m。 %Y 是四位数年份。 (格式元素列表位于 MySQL 文档中的 DATE_FORMAT 函数下。

引用:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

<小时/>

如果您的 timestart 列是 INTEGER 或其他数字数据类型，表示自一个时代开始以来的秒数或其他时间单位，您可以使用相同的方法来提高性能。

在谓词中，引用表中的裸列，并执行文字方面所需的任何转换。

如果您在插入行时没有使用 MySQL 函数转换为“自 1970 年 1 月 1 日 UTC 以来的秒数”(这实际上是 TIMESTAMP 数据类型在内部执行的操作)，那么我不建议使用 MySQL函数在查询中进行转换。

如果您要在 PHP 中将日期和时间转换为整数类型“时间戳”，那么我也会在 PHP 中进行逆转换，并修剪到午夜并添加一天PHP。

在这种情况下，您的 MySQL 查询将采用简单的形式:

  WHERE sd.timestart >= ? 
    AND sd.timestart <  ?

您将在其中传递适当的整数值，以与 INTEGER 时间戳列进行比较。

<小时/>

请注意，MySQL 确实提供了转换为“自 1970 年 1 月 1 日 UTC 以来的秒数”的函数，因此如果 timestart 是自 1970 年 1 月 1 日 UTC 以来的秒数，则类似以下内容是有效的:

WHERE sd.timestart >= UNIX_TIMESTAMP(STR_TO_DATE( '25 March 2014' , "%e %M %Y")) 
  AND sd.timestart <  UNIX_TIMESTAMP(STR_TO_DATE( '25 March 2014' , "%e %M %Y") + INTERVAL 1 DAY)

但是...再次注意时区转换问题；如果 MySQL 数据库的时区设置与 Web 服务器不同。如果您要存储“整数”，那么我不会将其与 MySQL 所做的转换搞混，这可能与 Web 服务器所做的转换函数不完全相同。