0

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.

fedorqui
  • 275,237
  • 103
  • 548
  • 598
user781470
  • 1

1 Answers1

0

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.

Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • Thanks again. It looks like your solution stores the gmt-offset (-05:00) in the user's record instead of the timezone name (US/Eastern). I considered this, but it doesn't seem to address the DST change. I suppose I could store both offsets (summer/winter) and then I'd just need to know which one to use based on the current time. Hmmm. But then don't some places change to DST at slightly different times of year? Hmmm. – user781470 Jul 10 '12 at 20:43