php/mysql查询时区敏感行的正确方法

Question

我将行用户作为行存储在数据库中。我允许用户输入他们的时区，它也存储在数据库中。

我的应用程序每天都会在他们指定的时间给他们发电子邮件。我将电子邮件传递作为一行存储在另一个数据库表中。这将通过cron作业实现。

我希望我的查询能够提取用户记录，其中该用户的最新电子邮件至少在1400分钟（24小时前），并且根据他们的时区超过了他们指定的时间。

我有以下查询，但我不知道如何解释mysql的时区和用户的时区。有什么建议吗？

SELECT u.id, u.email, u.timezone_offset, u.email_time, TIMESTAMPDIFF( 
MINUTE , CONCAT( DATE( MAX( e.created ) ) ,  ' ', u.email_time ) , NOW( ) ) AS min_since_last_email
FROM Users u
INNER JOIN Emails e ON e.user_id = u.id
WHERE min_since_last_email > 1400
GROUP BY u.id

时区偏移量是与GMT的偏移量。所以对我来说，它大约是"-5"。

Answer 1

稍微改变一下逻辑：用PHP进行计算，用UTC将所有内容存储在数据库中。然后是一个简单的摘录。您可以在PHP中使用时区函数很容易地做到这一点，并且可以按用户自定义-SQL语句是一个全局语句，按用户自定义更难（正如您所经历的）。

因此，"发送电子邮件的时间"可能是UTC凌晨4点（这在数据库中），但您会向用户显示"晚上9点"。

您可能遇到的一个问题是夏令时-当时区转入/转出夏令时时，您需要重新计算所有时间。然而，你目前的方法无论如何都不允许这样。。。

Answer 2

上次发送电子邮件的日期是您的日期，因此请保存该日期。

随着你发送越来越多的电子邮件，计算每个用户的最大值将变得越来越昂贵。取消规范化并在用户记录中放入该日期时间的副本。

然后，计算该日期的年龄和用户时区的偏移量。

select *, date_add(now(), interval timezone_offset hour) as email_expected_on
from users
where last_email_date <= date_add(now(), interval timezone_offset hour);

请注意，同样重要的是，不要将公式应用于users.last_email_date，否则将无法在该列上使用索引。