Store Open/Close Times and DST Changes

I've been stuck on this for two days and have gotten no where. I tend to think future and the future problems that will come around. My server's time is set to UTC and linux box is fully updated with the timezones as well as the data is in my database.

I'll explain my system for the best answer.

This site sells "items" but can only sell during open and closed times. The stores can have split hours: ie: open from 8am-12pm 1pm-8pm... etc.

So my hours table looks like:

id (1) | store_id (1) | opens (08:00) | closes (21:00)

Above has the sample data next to the column name. Basically store id#1 may be in Los Angeles (US/Pacific) or it may be in New York City (US/Eastern).

What is the best way to ensure that I don't miss an hour of downtime so I c开发者_运维知识库an disalow users to order from these stores during their off hours. If I'm off of the times one hour, that's one hour no one can order when they are really open and an hour users will order when they are really closed.. visa versa depending on time changes.

Has anyone dealt with this? And if so, how did you do it?

What is the best way I can go to solve this issue. I've been dealing with it and it's eating my brain for the past 48 hours.

Please help! :)

---

It's actually super easy to achieve in Postgres and MySQL. All you have to do is store the timezone in the user side, set server TZ to UTC, then convert between the two.

EX:

SELECT 
    CASE WHEN (
        (CAST((CURRENT_TIMESTAMP at time zone s.timezone) as time) BETWEEN h.opens AND h.closes) AND 
        h.day = extract(dow from CURRENT_TIMESTAMP at time zone s.timezone)) THEN 0 ELSE 1 END
    ) as closed 
FROM store s 
LEFT JOIN store_hours r ON s.id = r.store_id 
HERE h.day = extract(dow from CURRENT_TIMESTAMP at time zone s.timezone)

It's something like that. I had to do typecasting that way because I was limited for using Doctrine 1.2.

Works like a charm even with DST changes.

---

One thing to bear in mind is that some places (think Arizona) don't do DST. You might want to make sure that your database has enough information so you can distinguish between LA and Phoenix should that prove necessary.

Assuming you follow ITroubs' advice, and put offsets in the database (and possibly information about whether a store is in a DST-respecting locale), you could do the following:

Build your code so it checks whether DST is in effect, and builds your queries appropriately. If all your stores are in NY and LA, then you can just add 1 to the offset when needed. If not, you'll need a query which uses different rules for DST and non-DST stores. Something like,

SELECT store_id
FROM hours
WHERE
(supportsDST = true AND opens < dstAdjustedNow AND closes > dstAdjustedNow) 
OR (supportsDST = false AND opens < UTCNow AND closes > UTCNow)

If you go this route, I recommend trying to centralize and isolate the code that deals with this as much as possible.

Also, you don't mention this, but I assume that a store with split time would have two rows in the hours table, one for each block that it's open.