选择所有日期等于今天的时区差异

We would like to fetch all rows which we added today, we have column name dnt(timestamp), but our timezome is 12 hours ahead of the server time zone. So what would be SQL query to get current results. We are using following at present...

我们想要获取今天添加的所有行，我们有列名dnt（时间戳），但我们的timezome比服务器时区提前12小时。那么什么是SQL查询来获得当前结果。我们目前正在使用以下......

SELECT * FROM messages WHERE user = '$user' AND DATE(dnt) = CURDATE();

SELECT * FROM messages WHERE user ='$ user'AND DATE（dnt）= CURDATE（）;

But its not giving correct results due to timezone difference.

但由于时区差异，它没有给出正确的结果。

thanks.

谢谢。

2 个解决方案

#1

I do agree with @MarvinLabs - this is a design flaw.

我同意@MarvinLabs - 这是一个设计缺陷。

however if you wish to leave the design as it is you should use this instead:

但是如果你想保留设计原样，你应该使用它：

SELECT * 
FROM messages 
WHERE user = '$user' 
  AND DATE(CONVERT_TZ(dnt,'source_timezone','destination_timezone')) = CURDATE()

change the source-timezone and destination_timezone accordingly

相应地更改source-timezone和destination_timezone
MySQL CONVERT_TZ() Reference: here

MySQL CONVERT_TZ（）参考：这里

#2

I would suggest that all the dates shall be stored in GMT time zone. Then converted in the view at display time. This way, when you need to query some date, first you convert the client side date to GMT and pass it directly to your SQL query. SQL servers have some functions to get the GMT date rather than the server date. You can also pass the current date in the GMT time zone as a query parameter rather than using the SQL functions (using PHP functions to get the gmt date).

我建议所有日期都应存储在GMT时区。然后在显示时在视图中转换。这样，当您需要查询某个日期时，首先将客户端日期转换为GMT并将其直接传递给SQL查询。 SQL服务器具有一些函数来获取GMT日期而不是服务器日期。您还可以将GMT时区中的当前日期作为查询参数传递，而不是使用SQL函数（使用PHP函数获取gmt日期）。

See: gmmktime for example

参见：例如gmmktime

#1