Fix date and time columns in MySQL
I have a timezone mismatch. I need to adjust the table. All dates and times prior to the unix timestamp 1253568477 must have 5 hours added to their values to make them equal to GMT.
I have the following columns ...
date
(data type date
), off for -5 hours
time
(data type time
), off for -5 hours
timestamp
(datatype int
) this column is the correct time
The reason the time zones are disabled is due to the way I was setting the values when inserting rows ...
$sql = "insert into email_opens (userid, email, serial, date, time, timestamp) values (
'$userid',
'$opened_by',
'$serial_number',
now(),
now()',
'".gmmktime()."'
)";
The value now()
used the server's time zone (Eastern), while the value was gmmktime
specified as GMT. I have since corrected this query to always use GMT.
Is there a way to add 5 hours for columns time
and date
for those rows where timestamp <1253568477 in one batch query?
Clarification:
My goal is to update every wrong row in the table with the correct time by adding 5 hours to each value.
source to share
Try the following:
UPDATE mail_opens SET date = DATE_ADD(CONCAT(date, ' ', time), INTERVAL 5 HOUR), time = DATE_ADD(CONCAT(date, ' ', time), INTERVAL 5 HOUR);
And you probably need the following:
<?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 this:
<?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()."'
)";
?>
source to share
make sure you fix the problem so you only know to update entries that are less than the unix timestamp of your timezone.
Then update the field and add 60 * 60 * 5 (5 hours in seconds) to these entries.
So your request will be as follows:
UPDATE email_opens SET timestamp = (timestamp + 18000) WHERE timestamp < 1253568477;
Cool.
source to share