Tutorial :Large MySQL Update Query


After getting an answer from this thread, it would save me alot of retyping if any readers could refer to it: link text I find that I need to update(within a mysql db) a timezone code, singularly (e.g 10+, 1-, etc) to olsen code (e.g "Europe/London", etc) according to what already exists within the user_timezone column. What would be the easiest what to go about that?

Any ideas would be very appreciated.


Given that a timezone offset (e.g. UTC+2) may correspond to many entries in the Olson database (e.g. Europe/Sofia, Europe/Riga, Africa/Cairo, etc.) you have to choose beforehand the offset-to-Olson-code correspondence. Create a table describing those correspondences and then use it to build you update statement.


Unfortunately you've no way of knowing which zone a user is in from the UTC offset. If you take a look at Wikipedia's list of timezones, you'll notice that many zones share the same time offset, e.g. Europe/Sarajevo and Europe/Brussels


There is the DateTimeZone class you can work with but what you get is not the offset in hours but in minutes (IIRC).


The way to remap values in mysql looks like:

UPDATE `sometable`  SET `somecolumn` = IFNULL(ELT(FIELD(`somecolumn`,      'oldval1', 'oldval2', 'oldval3'),      'newval1', 'newval2', 'newval3'),      `somecolumn`  );  

That will change occurrences of 'oldval1' to 'newval1' and so on, while leaving alone anything not appearing in the 'oldval' list.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »