Large MySQL Update Query

Question

Hi all,

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.

Answer 1

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

Answer 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

Answer 3

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.

Answer 4

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.