Working with timezones and DST

[Keleth]

I was hoping someone could give me thoughts on what I've found to be a complicated matter. I'm trying to figure out how to best utilize timezones and dst. So far, I've gotten the script running that offers the GMT offset options (ie +01:00) from http://en.wikipedia.org/wiki/Time_zone#Standard_time_zones

One problem is some people may not know their GMT offset (or what GMT is...), so I was thinking of utilizing the list from here: http://us2.php.net/manual/en/timezones.others.php

However, I'm wondering how to handle DST. I could do it manually by paying attention, but I'm not sure if there is an automated way to do it, or if I should be doing it with PHP instead of SQL (although I'm having trouble working with dates/times in PHP, but thats a topic for another thread).

[Old Pedant]

I'm sorry, but I'm more than a little lost. How is the DB supposed to know the user's timezone??? By looking up his/her address in a table of timezones??

So what happens if I have a laptop and I travel 3 timezones away. If you use my home timezone, then any messages you give me while I travel will be wrong.

Seems to me that I would just keep all the dates and times in the DB in some universal time (UTC or maybe just my server's timezone). And then, when a person connects, use JavaScript code in the browser to detect the time on their computer and note the difference between it and UTC and use that as a recorded offset, if I want that. Just round the difference to the nearest half hour (half because there *are* timezones--India, for example--which are not integer hours off of UTC).

[Keleth]

Haha, sorry I was unclear. One of the options the users enters and which is stored is their entry is their offset. Javascript is something I'll be adding after, but as ever, I need to make sure the site works without Javascript as well.

All dates are currently stored in PDT, which is my servers default TZ, then I'm converting it using CONVERT_TZ to the local time. I guess I was wondering if anyone had thoughts on how to work with daylight savings time.

[Old Pedant]

Well, what do you do about the fact that the dates for DST differ, around the world? And in Russia the system is quite a bit different (or was, when I studied it, back in USSR days).

If you only care about USA, and if you are going to make the user specify his/her timezone anyway, then yes, also have a checkbox that says "my timezone uses DST".

But, again, the better way to do this would be to auto-detect the time difference by sending the client's time from the browser to your server, using JS in the browser to get that time. Avoid asking the user completely. And you don't even care about DST.

Won't matter if the person is in PDT (CA) or MST (AZ), you will see it as a zero offset and be happy.