query to change datetime in php !

[hassank1]

I've a cloumn "date" in my db of type datetime ..

 

I want to add 8 hours for all the rows in the column date in my table..

 

how can I do that ?

 

thx

[marcusfaye87]

<?php

function split_date ($dateTime)
{
$splitTime ['year'] = substr ($dateTime,0,4);
$splitTime ['month'] = substr ($dateTime,5,2);
$splitTime ['day'] = substr ($dateTime,8,2);
$splitTime ['hour'] = substr ($dateTime,11,2);
$splitTime ['minute'] = substr ($dateTime,14,2);
$splitTime ['second'] = substr ($dateTime,17,2);	

return $splitTime;
}

$loop = 0;
$query = "SELECT `date` FROM `table`";
$result = mysql_query ($query);

while ($row = $mysql_fetch_array($result))
{
$loop++;

$dateTime = split_date ($row['date']);

$dateTimeTemp = $dateTime['hour'] + 8;

if ($dateTimeTemp > 24)
{
	$dateTime['day']++;
	$temp = 24 - $dateTime['hour'];
                $dateTime['hour'] = 8 - $temp
}
else
{
	$dateTime['hour'] = $dateTime['hour'] + 8;
}

$dateTimeNew = "$dateTime[year]-$dateTime[month]-$dateTime[day] $dateTime[hour]:$dateTime[minute]:$dateTime[second]";

$query = "UPDATE `table` SET date='$dateTimeNew' WHERE id='$loop'";
        mysql_query ($query);

}

?>

 

This should do the trick. You still need to fill in your table name.. I just put "table" there

 

Edit, sry now it's correct

[revraz]

Can't you do just a simple + 8 hours to a UPDATE?

[marcusfaye87]

Can't you do just a simple + 8 hours to a UPDATE?

 

I have no idea since it's no timestamp but an actual formated MySQL DATETIME.

Mine will do the job no matter what.

 

But I don't know for better alternatives.

he can just copy/paste this.

[revraz]

MySQL supports addition of hours.

[marcusfaye87]

I had no idea, this is an alternative I did.

But can you go more simple than that?

 

<?php

function split_date ($dateTime)
{
$splitTime ['year'] = substr ($dateTime,0,4);
$splitTime ['month'] = substr ($dateTime,5,2);
$splitTime ['day'] = substr ($dateTime,8,2);
$splitTime ['hour'] = substr ($dateTime,11,2);
$splitTime ['minute'] = substr ($dateTime,14,2);
$splitTime ['second'] = substr ($dateTime,17,2);	

return $splitTime;
}

$loop = 0;
$query = "SELECT `date` FROM `table`";
$result = mysql_query ($query);

while ($row = $mysql_fetch_array($result))
{
$loop++;

$splitTime= split_date ($row['date']);

$dateTimeNew = mktime ($splitTime['hour']+8, $splitTime['minute'], $splitTime['second'], $splitTime['month'], $splitTime['day'], $splitTime ['year']);

$dateTimeNew = date ('Y-m-d H:i:s', $dateTimeNew);

$query = "UPDATE `table` SET date='$dateTimeNew' WHERE id='$loop'";
        mysql_query ($query);

}
?>

[hassank1]

thanks that exactly what I want .. however I think it need some modification because what if day is 30 then +1 .. I should add +1 to months etc... right ??

 

one more thing plz .. to get the current date I am using

 

$DateNow  = date("Y-m-d H:i:s");      //date @ this moment

 

however how can I get the current date + 8 hours ...???

 

thx

 

revraz -> I've tried ur method it didn't work :S ... can u give me ur query to test it ! maybe I've done something wrong in my query..

[marcusfaye87]

my 2nd one should do the trick, mktime() atomatically updates the date.

So if you add 8 hours it will automatically alter everything to make it fit

 

Yes the G should be a H in that one, my bad x_x

[hassank1]

$dateNOW = mktime ($splitTime['hour']+;

 

$dateNOW = date ('Y-m-d H:i:s', $dateNOW );

 

would this give me the current time + 8 hours ??

[marcusfaye87]

No, don't do that... the $splitTime['hour'] will surpass 24 hours

 

god I suck at coding in a browser, second!

[hassank1]

OK ..

 

but now how can I save into a variable the current time + 8 hours .. (I mean this time I don't get the value from a table .. rather I want to use the current time and add 8 hours to it )

 

<-- that's another question .. however it's kind of similiar ..

 

thanks .. btw sorry because u r coding in a browser

[DarkWater]

$now = time();

$nowplus8 = strtotime("+8 hour", $now);

[marcusfaye87]

<?php

// Splits the date
function split_date ($dateTime)
{
$splitTime ['year'] = substr ($dateTime,0,4);
$splitTime ['month'] = substr ($dateTime,5,2);
$splitTime ['day'] = substr ($dateTime,8,2);
$splitTime ['hour'] = substr ($dateTime,11,2);
$splitTime ['minute'] = substr ($dateTime,14,2);
$splitTime ['second'] = substr ($dateTime,17,2);	

return $splitTime;
}

// Set the loopcounter to 0
$loop = 0;

// Get the MySQL data
$query = "SELECT `date` FROM `table`";
$result = mysql_query ($query);

// Loop the rows
while ($row = $mysql_fetch_array($result))
{
// Increment the loop variable every loop
$loop++;

// Get the splitted time array
$splitTime = split_date ($row['date']);

// Make new time (adds the 8 hours)
$dateTimeNew = mktime ($splitTime['hour']+8, $splitTime['minute'], $splitTime['second'], $splitTime['month'], $splitTime['day'], $splitTime['year']);

// Format the timstamp into a MySQL DATETIME format
$dateTimeNew = date ('Y-m-d H:i:s', $dateTimeNew);

// Update the row
$query = "UPDATE `table` SET date='$dateTimeNew' WHERE id='$loop'";
    mysql_query ($query);

}
?>

 

I added some comments, this one should work though

and for the current date that's fairly simple

 

<?php

$dateNew = mktime (date ('H')+8, date ('i'), date ('s'), date ('m'), date ('d'), date ('Y'));
$dateNew = date ('Y-m-d H:i:s', $dateNew) ;

?>

[marcusfaye87]

$now = time();

$nowplus8 = strtotime("+8 hour", $now);

 

time () creates a timestamp, but he needs it formatted to DATETIME too which is Y-m-d H:i:s

[DarkWater]

Didn't know he needed DATETIME format.

 

$now = time();

$nowplus8 = date("Y-m-d H:i:s", strtotime("+8 hour", $now));

 

Have a blast.

[PFMaBiSmAd]

You can do this directly in a query using the mysql date_add() function - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add

 

You can also use the current date or datetime directly in a query using CURDATE() for just the date or NOW() for a datetime.