Check opening hours in different timezones

Question

My Specs:

• Postgres 9.6.6, latest Ubuntu LTS

• Server Timezone is GMT

• A table with two columns that shows store opening and closing times, with each timezone.

Here's the table:

ShopId      OpenAt           CloseAt
  1      09:00:00 -08     17:00:00 -08
  2      09:30:00 -05     17:30:00 -05
  3      08:00:00 -11     15:00:00 -11
  4      10:00:00 +07     15:30:00 +07

What I need to know is if at moment (at my current GMT time), the shop is open. Taking into consideration that Saturday and Sunday it's closed.

I'm digging around and I got something like:

 SELECT ((OpenAt,CloseAt) OVERLAPS(NOW())) AND ISODOW < 6  

with no luck...

Thanks
Perez

Answer 1

Try this :

SELECT ((date_trunc('day',nowAtShopLocation)+"OpenAt"::time, date_trunc('day',nowAtShopLocation)+"CloseAt"::time) OVERLAPS(nowAtShopLocation,nowAtShopLocation)) and EXTRACT (ISODOW FROM nowAtShopLocation) <6
from (
  select *,now() AT TIME ZONE 'UTC'+(EXTRACT(TIMEZONE_HOUR FROM "OpenAt")||' hour')::interval nowAtShopLocation from your_table
) a