其中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();

现在，它显示了我为1月24日在我的日历上的第25天预订。

问题似乎在于查询的这一部分：

            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 ()函数回显时间戳，确保所有时区对齐正确。

为什么会发生这种情况？任何帮助都将不胜感激。

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引擎所在的时区，因为这是您正在进行转换的位置。

我将从应用程序中正确的日期时间(例如，在00:00:00时第25次)计算出确切的时间戳，并将其传递给查询。

转换本身：

 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。