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
Update a MySQL field/Timezone
Moderator: General Moderators
Re: Update a MySQL field/Timezone
Please post the output of SHOW CREATE TABLE - timezone handling varies by column types in mysql.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Update a MySQL field/Timezone
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]
[text]
UPDATE `table_name` SET `Date` = ADDTIME(`Date`, -4) WHERE `ID` < 1000001[/text]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Update a MySQL field/Timezone
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:
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
Thanks AbraCadaver. I will give this a try.