[eluser]chuckleberry13[/eluser]
I've been looking into this as well and if your mysql server has the [email=http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html]mysql timezone files[/email] loaded then it can handle all the daylight savings adjustments automatically. Then all you have to do is set the timezone to the user's timezone per connection like this before you run your query.
So a query would look like this
Code:
SET time_zone = 'US/Eastern';
SELECT * WHERE `timestamp` > CURDATE()
Timezone variables can also be values like this 'UTC' or '+10:00' or '-6:00'.
To test wether or not your mysql server has the timezone tables loaded run this query
Code:
SELECT COUNT( * )
FROM mysql.time_zone_name
My problem is my host currently doesn't have these tables loaded so I'm having to look into a php solution which the daylight savings issues seems like a guessing game. So I figured if you guys had these tables loaded it could save you a lot of trouble and get you accurate daylight savings times.
This is from the mysql documentation page "Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:
mysql> SET time_zone = timezone;
The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval."