php - Timezone specific "Open Now" function -

i need 'is open now' php function on site building. looking @ previous question, have built 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   opening_hours   dow=dayofweek(curdate())     , loc_id=:loc_id" 

i can work out if open or not. product building global , need 'open now' related timezone location in, not server location or viewers location.

i storing country code , lat/lng of location in related db, thoughts obtain timezone that, or provide method user select one, , modify sql somehow.

am heading in right direction? method be?

1. read time zone support in mysql, , ensure mysql database configured current time zone tables. update regularly.

2. associate each location named iana/olson time zone, such "america/los_angeles", or "europe/london". refer the list here. if have lat/lon, can time zone via one of these methods.

3. use mysql convert_tz function convert current utc time specific zone. example, convert_tz(utc_timestamp(),'utc','asia/tokyo')

4. use day-of-week , time of day of converted time check against day , range in location's entry.

also, note others may suggest taking approach of storing utc in database, or of converting of values utc before comparing against "now" value. either of approaches can fail in edge cases, since utc day-of-week not same day-of-week in each time zone.

one other approach will work, takes more effort, predetermine specific utc starting , stopping times amount of time in future (at least next one, perhaps further). can scan list utc time. works better @ scale, when have thousands or more individual entries check. @ smaller scales, it's not worth overhead.

likewise, have background process sets "now open" flag on each record, have working against database, , never check other times "now".