Timezone specific "Open Now" function

Question

I need an 'is open now' php function on a site I am building. Looking at a previous question, I have built my mysql table:

hours_id      INT         NOT NULL,
loc_id        INT         NOT NULL,
dow           TINYINT     NOT NULL,
open_time     TIME        NOT NULL,
close_time    TIME        NOT NULL,

Then using:

"SELECT open_time, close_time 
 FROM opening_hours 
 WHERE dow=dayofweek(curdate()) 
   AND loc_id=:loc_id"

I can then work out if it is open or not. The product I am building is global and I will need the 'open now' to be related to the timezone the location is in, not the server location or the viewers location.

I am already storing the country code and lat/lng of the location in a related db, so my thoughts are that I obtain the timezone from that, or provide a method for the user to select one, and then modify my SQL somehow.

Am I heading in the right direction? What would your method be?

Answer 1

1. Read about time zone support in MySQL, and ensure your mysql database is configured with current time zone tables. Update regularly.

2. Associate each location with a named IANA/Olson time zone, such as "America/Los_Angeles", or "Europe/London". Refer to the list here. If you have lat/lon, you can look up the time zone via one of these methods.

3. Use the MySQL CONVERT_TZ function to convert the current UTC time to the specific zone. For example, CONVERT_TZ(UTC_TIMESTAMP(),'UTC','Asia/Tokyo')

4. Use the day-of-week and time of day of the converted time to check against the day and range in the location's entry.

Also, note that others may suggest taking the approach of only storing UTC in the database, or of converting all of the values to UTC before comparing against the "now" value. Either of those approaches can fail in edge cases, since the UTC day-of-week is not necessarily the same day-of-week as in each time zone.

One other approach that will work, but takes more effort, is to predetermine the specific UTC starting and stopping times for some amount of time in the future (at least to the next one, but perhaps further). Then you can scan this list with the UTC time. This works better at scale, when you have thousands or more individual entries to check. But at smaller scales, it's usually not worth the overhead.

Likewise, you could have a background process that just sets a "now open" flag on each record, but it would have to constantly be working against your database, and you could never check for other times than "now".