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:

    time_zone_name (eg, "US/Eastern")

    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 (
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.




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 thisdate('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 usedate() again to format the dates/times into their local format type.

People are also looking for solutions to the problem: Multi level menu with PHP/MySQL


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.