How to search based on local time, accounting for timezones -
i have collection of shops opening hours , want know of them open.
the problem shops located on world.
this find query looks (i use sql example, doesn't matter):
select shops.name shops left join shops_openhours on shops_openhours.shopid=shop.id shops_openhours.day=[day of week] , shops_openhours.opentime < [now time] , shops_openhours.closetime > [now time]
when store working time in local time can't search because of timezones.
when store working time in utc can't search because of daylight saving time.
some sample records:
query:
select shops.name, shops_openhours.day day_of_week, shops_openhours.opentime, shops_openhours.closetime shops left join shops_openhours on shops_openhours.shopid=shop.id
result:
name day_of_week opentime closetime shop1 'monday' '09:00' '18:00' shop1 'tuesday' '09:00' '18:00' shop2 'tuesday' '10:00' '19:00' shop2 'tuesday' '10:00' '19:00'
sample query (now monday 09:30):
select shops.name shops left join shops_openhours on shops_openhours.shopid=shop.id shops_openhours.day='monday' , shops_openhours.opentime < '09:30' , shops_openhours.closetime > '09:30'
result:
name shop1
Comments
Post a Comment