Correct Date and Time Columns in MySQL

Question

I have a timezone mismatch I need to correct in a table. All dates and times before unix timestamp 1253568477 need to have 5 hours added to their values to make them equal GMT.

I have the following columns...

date (data type date), off by -5 hours

time (data type time), off by -5 hours

timestamp (data type int), this column is the correct time

The reason the timezones are off is because of the way I was setting the values when inserting the rows...

$sql = "insert into email_opens (userid, email, serial, date, time, timestamp) values (
'$userid',
'$opened_by',
'$serial_number',
now(),
now()',
'".gmmktime()."'
)";

The now() value was using the server timezone (eastern) whereas the gmmktime value specified GMT. I have since corrected this query to always use GMT.

Is there a way I can add 5 hours to both time and date columns for those rows where timestamp < 1253568477 in one batch query?

Clarification:

My goal is to update each incorrect row in the table with the correct time by adding 5 hours to each value.

Answer 1

Try with this:

UPDATE mail_opens SET date = DATE_ADD(CONCAT(date, ' ', time), INTERVAL 5 HOUR), time = DATE_ADD(CONCAT(date, ' ', time), INTERVAL 5 HOUR);

And then you probably need this:

<?php
    echo date('Y-m-d H:i:s')."<br>";
    $addhours = strtotime('+5 hour');
    echo $addhours."<br>";
    $newdate = date('Y-m-d H:i:s', $addhours);
    echo $newdate;
?>

So, using that:

<?php
$addhours = strtotime('+5 hour');
$newdate = date('Y-m-d H:i:s', $addhours);
$sql = "insert into email_opens (userid, email, serial, date, time, timestamp) values (
'$userid',
'$opened_by',
'$serial_number',
'$newdate',
'$newdate',
'".gmmktime()."'
)";
?>

Answer 2

sure, work out the time you corrected the problem so you know only to update records that have a value less than the unix timestamp of your timezone change.

Then update the field and add 60 * 60 * 5 (5 hours in seconds) to those records.

So, your query would be the following:

UPDATE email_opens SET timestamp = (timestamp + 18000) WHERE timestamp < 1253568477;

Cool.