Timezone offsets (and st/dst changes) in MySQL / PHP

Question

Basically I have a site that allows stores to store their open/close times.

Now, I'm trying to add the feature of giving their visitors the ability to see if the store is closed.

I understand I can run a query like:

SELECT * FROM `store_hours` WHERE TIME(NOW()) BETWEEN `opens` AND `closes`

... and the query works and retrieves the stores that are currently open. However, at first this query didn't put into consideration the store's timezone.

SO.. I decided to store the store's open/close times with the conversion to UTC attached:

id | store_id | opens | closes | utc_opens | utc_closes

Now I can query like so:

SELECT * FROM `store_hours` WHERE TIME(NOW()) BETWEEN `utc_opens` AND `utc_closes`

So now I can easily get a global list of how many stores are open and it doesn't even matter where they are.

Now I came to this problem. Through PHP, all I did was add the offset to the timestamp:

date('H:i', strtotime($values['closes']) + $offset)

Then I started thinking about Pacific Standard Time and then Daylight Savings Time.

I want to add this open/close feature and it will become a huge part of the system. It's just that I will lose out on a lot of income if I'm off by an hour.

What's the best way to achieve what I'm trying to accomplish?

Answer 1

Try this:

http://stackoverflow.com/questions/13/how-can-i-determine-a-web-users-time-zone

Answer 2

I've never dealt with this before (nor do I ever wish to, because it's an annoyingly complex problem). That said, there are methods to get around this:

http://stackoverflow.com/questions/2148237/how-do-you-deal-with-timezone-issues

It seems that if you want your system to work not perfectly, but Pretty Damn Well, you will have to dive into something like this:

http://en.wikipedia.org/wiki/Zoneinfo

In short, the problem is one of granular complexity. No matter how hard you try, you can't write a simple script that will determine a store's exact time given only a static GMT offset. To overcome this issue, people have put together public databases that account for (nearly) all of the little nooks and crannies inherent in time zones. Even if you know that a user is in the Western US (west of the Rockies), you don't know if he's in Barstow or Phoenix (Arizona doesn't do DST, so half the year it's got the same time as LA, and the other half it's the same as Denver).

So back to data entry you go...you just need to put in the exact time zone code for each store and query that zoneinfo database to determine what the current offset should be.