Page 1 of 1

Update a MySQL field/Timezone

Posted: Thu Jun 24, 2010 10:45 am
by spec36
I currently have a database that has a ID column and a Date column. The date column shows the date and time of the entry. There are approx 1 million records already in the database that have the incorrect timezone on the time. All new entries to the database have the time zone corrected. I am looking for a way to update/convert all the already existing date records to the correct timezone. The records that need to be updated are in the id range of 0-1000000, everything after 1000000 is corrected on insertion to the database table.

Example of my Table:

ID Column: 31615463
Date Column: 2010-06-24 13:59:06 | Note: I need this to change to -4 hours 2010-06-24 9:59:06

Your help is greatly appreciated.

Thanks

Re: Update a MySQL field/Timezone

Posted: Thu Jun 24, 2010 10:54 am
by Weirdan
Please post the output of SHOW CREATE TABLE - timezone handling varies by column types in mysql.

Re: Update a MySQL field/Timezone

Posted: Thu Jun 24, 2010 11:03 am
by AbraCadaver
I haven't tested but this might get you on the right track (assuming MySQL):
[text]
UPDATE `table_name` SET `Date` = ADDTIME(`Date`, -4) WHERE `ID` < 1000001[/text]

Re: Update a MySQL field/Timezone

Posted: Thu Jun 24, 2010 11:06 am
by spec36
Here is the column information

Field: id
Type: int(10)
Collation:
Attributes: UNSIGNED
Null: No
Extra: auto_increment

Field: date
Type: datetime
Collation: latin1_swedish_ci
Attributes:
Null: Yes
Extra:

Re: Update a MySQL field/Timezone

Posted: Thu Jun 24, 2010 11:06 am
by spec36
Thanks AbraCadaver. I will give this a try.