Store Open/Close Times and DST Changes

Question

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 can 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! :)

Answer 1

One thing to bear in mind is that some places (think Arizona) don't do DST. You might want to make sure that your db 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 db (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.