perplexed by adding to a timestamp

[ariev]

I need help with using PHP to add n seconds, days or weeks to a given mysql timestamp (not current day/time but a set day/time) and return the result in the same format.

For example:
"20031229121500" is the mysql record pulled from a DATETIME field.

So I need to do this with PHP:
(20031229121500 + 4 hours) = 20031229121900
(20031229121500 + 1 day) = 20031230121500
(20031229121500 + 30 seconds) = 20031229121530

Can anyone SHOW me exactly how this is done, with a code example? I *think* is has something to do with converting the mysql datetime record to a UNIX timestamp, adding or subtracting from this, then converting this BACK to the mysql datetime format. But how?

Thanks for the help!
-Arie

[edited by: ariev at 3:56 am (utc) on Dec. 1, 2003]

[amoore]

I personally would make my database do the work as described in the mysql manual at [mysql.com...]

For instance:
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

Hope it helps.

-Andy

[ariev]

But I don't want to use the "current" time, but a static date/time, say from the past or future. Like adding 1 year to 1990 to get 1991, but using the mysql TIMEDATE format in PHP.

[ariev]

OK, I finally found what I was looking for. I hope these functions will help other people who are looking to do this without a lot of hassle. :)

function returnSQLTimestamp($timestamp)
{
// Function returnSQLTimestamp
// by elnino (25-03-2002)
// version 0.1

// Converts an Unix-timestamp to a MySQL-timestamp.

return strftime('%Y%m%d%H%M%S', $timestamp);
}

function returnUnixTimestamp($timestamp)
{
// Function returnUnixTimestamp
// by elnino (25-03-2002)
// version 0.1

// Converts an MySQL-timestamp to a Unix-timestamp.

$year = substr($timestamp, 0, 4);
$month = substr($timestamp, 4, 2);
$day = substr($timestamp, 6, 2);
$hour = substr($timestamp, 8, 2);
$min = substr($timestamp, 10, 2);
$sec = substr($timestamp, 12, 2);

return mktime($hour, $min, $sec, $month, $day, $year);
}

function AddSecToSqlDate($timestamp, $seconds)
{
// Function returnUnixTimestamp
// by elnino (25-03-2002)
// version 0.1

// Converts an MySQL-timestamp to a Unix-timestamp.

$year = substr($timestamp, 0, 4);
$month = substr($timestamp, 4, 2);
$day = substr($timestamp, 6, 2);
$hour = substr($timestamp, 8, 2);
$min = substr($timestamp, 10, 2);
$sec = substr($timestamp, 12, 2);

return returnSQLTimestamp(mktime($hour, $min, $sec+$seconds, $month, $day, $year));
}

function SubtSecFromSqlDate($timestamp, $seconds)
{
// Function returnUnixTimestamp
// by elnino (25-03-2002)
// version 0.1

// Converts an MySQL-timestamp to a Unix-timestamp.

$year = substr($timestamp, 0, 4);
$month = substr($timestamp, 4, 2);
$day = substr($timestamp, 6, 2);
$hour = substr($timestamp, 8, 2);
$min = substr($timestamp, 10, 2);
$sec = substr($timestamp, 12, 2);

return returnSQLTimestamp(mktime($hour, $min, $sec-$seconds, $month, $day, $year));
}

[DrDoc]

Well, you don't have to use the current date... You can use whatever date you want.

UPDATE tablename SET field=field+INTERVAL 4 DAY
SELECT field-INTERVAL 43 SECOND FROM tablename WHERE 1

[g1smd]

(20031229121500 + 4 hours) = 20031229121900

* cough *

(20031229121500 + 4 hours) = 20031229161500

.

Hmm, intriguiging the elnino wrote that script on 25-03-2002 rather than on 2002-03-25.