0

I am trying to find a way to query an 'opening times' table, allowing for timezones of each location. If it returns any rows, I know it is open.

I am storing this:

location_hours
int     loc_id        1
int     dow           1-7
time    open_time     09:00:00
time    close_time    18:00:00

locations
int     loc_id        1
varchar timezone      Europe/London

I initially expected to use:

SELECT l.loc_id 
FROM lh.locations_hours 
INNER JOIN locations l ON lh.loc_id = l.loc_id 
WHERE dow=dayofweek(CONVERT_TZ(UTC_TIMESTAMP(),'UTC', l.timezone)) 
AND CONVERT_TZ(UTC_TIME(),'UTC', l.timezone) >= lh.open_time 
AND CONVERT_TZ(UTC_TIME(),'UTC', l.timezone) <= lh.close_time

however, it appears mysql doesn't support using UTC_TIME() inside CONVERT_TZ.

What is a good alternative to this?

Paul
  • 578
  • 1
  • 8
  • 23
  • it'd be more efficient to just store everything as UTC times. doing conversions like that precludes using indexes. if your table gets "big", performance is going to be crap. – Marc B Jul 28 '15 at 20:03
  • @MarcB I decided against that after this question was answered. https://stackoverflow.com/questions/31549233/timezone-specific-open-now-function. You're right though... I've been thinking from an 'OPEN' type badge on a single profile, but my search function would be very slow. I'll leave this here as my answer may help someone with a single row basis, and I'll rethink my plans. Thanks. – Paul Jul 28 '15 at 20:13

1 Answers1

0

This works. I don't know if it is very efficient as it feels clunky.

SELECT l.loc_id 
FROM lh.locations_hours
INNER JOIN locations l ON lh.loc_id = l.loc_id 
WHERE dow=dayofweek(CONVERT_TZ(UTC_TIMESTAMP(),'UTC', l.timezone)) 
AND BINARY SUBSTRING(CONVERT_TZ(UTC_TIMESTAMP(),'UTC', l.timezone), 12, 8) >= lh.open_time 
AND BINARY SUBSTRING(CONVERT_TZ(UTC_TIMESTAMP(),'UTC', l.timezone), 12, 8) <= lh.close_time
Paul
  • 578
  • 1
  • 8
  • 23
  • 1
    Somewhat less clunky would be `TIME(CONVERT_TZ(UTC_TIMESTAMP(),'UTC', l.timezone)) >= lh.open_time`... also, the optimizer isn't going to evaluate this expression for each row (or yours, for that matter). It will resolve the expression to a constant value before comparison to the rows in the table, and will use viable indexes if they exist. – Michael - sqlbot Jul 28 '15 at 23:56