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!
CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')
CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
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.
Answer
Solution:
if you are using the lookup table to get the difference, I am sure that you are asking the site user where they are. Once they enter in their location, you can easily use some PHP to set their localisation through your scripts. This is pretty easy with php localisation. The list of timezones really is quite large. While it does contain your example of US/Eastern, it is not recommended that you implement t as it is only maintained (under 'Others') for backwards compatibility.
Then you can use php date using this
date('Z')to get their offset in seconds. If you store your datetime fields as unix timestamps it will be a trivial matter for you to add or subtract their offset. This way you are normalizing the data YOU store and keeping it at a zero offset (GMT).When you query the data in the database, simply add their offset which you got in the step above to the resulting output. You can even use
date()again to format the dates/times into their local format type.