Tutorial :Large MySQL Update Query



Question:

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.


Solution:1

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.


Solution:2

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


Solution:3

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


Solution:4

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
Previous
Next Post »