php - WHERE DATE_FORMAT = DATE_FORMAT 不准确

Question

我在我的数据库中为每个预订使用 unix 时间戳整数存储时间。我遇到了一个问题，我的查询没有正确选择内容，而且似乎休息了一天。

例如，在我的网站上，我有一个日历表，它显示了一个月中的每一天。我使用以下代码来填充每天的产品:

    $sql = "select b.*, p.name as p_name, p.color as p_color
            from `bookings` as b
            left join `products` as p on (p.id = b.id_product)
            where date_format(from_unixtime(b.timestamp), '%M %e %Y') = date_format(from_unixtime(:timestamp), '%M %e %Y')
            order by b.timestamp asc";
    $stm = $this->app->db->prepare($sql);
    $stm->bindParam(':timestamp', $this->timestamp);
    $stm->execute();
    $res = $stm->fetchAll();

现在，它会在我的日历中的第 25 天显示我为 1 月 24 日所做的预订。

问题似乎在于这部分查询:

            where date_format(from_unixtime(b.timestamp), '%M %e %Y') = date_format(from_unixtime(:timestamp), '%M %e %Y')

例如，当$this->timestamp = '1453698000'(第25个)和b.timestamp = '1453618800'(第24个)时，显示这条记录在我日历中的 25 日。

我完全不知道为什么会这样。我试过将 date_format 查询更改为使用“%d-%m-%Y”，我试过添加“00:00:00”，我试过在 PDO 构造中手动设置时区，我已经通过使用 PHP date() 函数回显时间戳，确保我所有的时区都正确排列(确实如此)。

为什么会这样？任何帮助将不胜感激。

Answer 1

首先，您可以通过运行以下命令更彻底地调试它:

SELECT date_format(from_unixtime(1453698000), '%M %e %Y')

和

SELECT date_format(from_unixtime(1453618800), '%M %e %Y')

并查看每个结果的结果。这应该揭示了问题。

我不知道您认为在传递 unix 时间戳时如何设置时区，但我认为这是不可能的。时区用于根据通用时间戳计算本地时间和日期。

PHP 认为每个时间戳位于哪个时区并不重要，这就是您的 PHP date() 函数匹配的原因。重要的是您的 MySQL 引擎所在的时区，因为这是您进行转换的位置。

我会根据您应用程序中正确的日期时间(例如 25 日 00:00:00)计算出准确的时间戳，并将其传递给查询。

转换本身:

 where date_format(from_unixtime(b.timestamp), '%M %e %Y') = date_format(from_unixtime(:timestamp), '%M %e %Y')

使时间戳上的任何索引无效，因此可能导致性能下降。

最好这样写:

 WHERE b.timestamp >= :timestamp
   AND b.timestamp <  :timestamp + INTERVAL 1 DAY

您的其他选择是更改 timezone of mysql .