1
UPDATE bridge_copy SET 
      game_begin = CONVERT_TZ(game_begin,'EST','GMT'), 
      game_end = CONVERT_TZ(game_end,'EST','GMT');

This is the query I attempted to run, and it failed misserably. What the end result was, was 2 columns filled with 0000-00-00 00:00:00 Rather than there conversions. Any ideas how I can update 12k+ rows without looping over them via some server side script that selects the current value, converts it, then updates it?

fedorqui
  • 275,237
  • 103
  • 548
  • 598
chris
  • 36,115
  • 52
  • 143
  • 252

1 Answers1

1

You have the right idea, but you need to use time zone names that are in the mysql.time_zone_name table. Often this is empty and needs to be populated.

For me the time_zone_name table was empty so I ran (on Ubuntu linux):

  mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -u root mysql

Then I was able to test this out using:

 SELECT CONVERT_TZ(game_end, "US/Eastern","GMT") from 
 bridge_copy;

After confirming that it does what is expected, then I can go ahead and do the update. Note I am using "US/Eastern", just make sure to pick something from the mysql.time_zone_name table.

See the docs for more time zone information. http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

Shawn Balestracci
  • 7,380
  • 1
  • 34
  • 52