Update a MySQL field/Timezone

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
spec36
Forum Commoner
Posts: 28
Joined: Thu Nov 19, 2009 6:07 pm

Update a MySQL field/Timezone

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Update a MySQL field/Timezone

Post by Weirdan »

Please post the output of SHOW CREATE TABLE - timezone handling varies by column types in mysql.
User avatar
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

Post 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]
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.
spec36
Forum Commoner
Posts: 28
Joined: Thu Nov 19, 2009 6:07 pm

Re: Update a MySQL field/Timezone

Post 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:
spec36
Forum Commoner
Posts: 28
Joined: Thu Nov 19, 2009 6:07 pm

Re: Update a MySQL field/Timezone

Post by spec36 »

Thanks AbraCadaver. I will give this a try.
Post Reply