php - MySQL: Wondering how to select records based on day, after adjusting for timezone
I want to select records that are "currently available" based on specifics in the tables. I think it should be easy but, of course, I'm not seeing it. I'm hoping that asking here will jog my head enough that maybe I can answer my own question!
I have 2 tables:
table_1 id time_zone_name (eg, "US/Eastern") table_2 tab1_id day_of_week (1=Monday, 7=Saturday)
Given that I know the current GMT timestamp is, say, "2012-07-08 13:35:00 GMT", I should be able to select records from table_1 where table_2.day_of_week is a match after adjusting for time-zone.
The MySql function CONVERT_TZ looked promising!
I could have tried this:
SELECT id from table_1 inner join table_2 ON (table_1.id=table_2.table_1_id)
WHERE DAYOFWEEK(CONVERT_TZ(UTC_TIMESTAMP, 'GMT', table_1.time_zone_name)) = table_2.day_of_week
but the CONVERT_TZ function doesn't accept zone names like "US/Eastern" on the MySql installed at my host. According to the MySql doc:
To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up.
Apparently they're not properly set up.
Is there a better way to go about this? Any suggestions would be greatly appreciated.