select all dates is equal to today with time zone difference

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...

SELECT * FROM messages WHERE user = '$user' AND DATE(dnt) = CURDATE()开发者_JS百科;

But its not giving correct results due to timezone difference.

thanks.

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).

See: gmmktime for example

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

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

• MySQL CONVERT_TZ() Reference: here