Timezone - convert timestamp to client timezone before storing in database

[ChiChee]

I posted in the following thread http://phpbuilder.com/board/showthread.php?t=10313337
but I'm thinking it's marked resolved so won't get any attention. My apologies for multiple postings.

---

I've found this post to be very helpful as this is exactly what I'm trying to accomplish. However, my PHP skills are minimal at best and I'm not understanding how to do this exactly. Here's what I've done to try to implement your suggestion:

I've added these lines just before the end of my form:

<?php
$offset = -8*3600;
$current_local_time = time() + $offset;
date("h:i:s", $current_local_time);
?>

and in my insert section I have:

if ((isset($POST["MM_insert"])) && ($POST["MM_insert"] == "insertForm")) {
$insertSQL = sprintf("INSERT INTO user_tbl (user_id, user_password, role, user_first_name, user_last_name, date_added) VALUES (%s, %s, %s, %s, %s, %s)",
GetSQLValueString($POST['user_id'], "text"),
GetSQLValueString($POST['password'], "text"),
GetSQLValueString($POST['role'], "int"),
GetSQLValueString($POST['first_name'], "text"),
GetSQLValueString($_POST['last_name'], "text"),
GetSQLValueString(date("Y-m-d H:i:s"), "date"));

but the time that gets stored in my database is still the server time which is 3 hours ahead of my time. What am I missing (besides knowledge and ability 😕 )?

Thank you for taking the time to respond. I'm sure this is a simple problem but I'm simpy stuck.

[RyanJones]

Well, there is a function called strtotime(). Using this you can take 3 hours away from the current time like so:

<?php
  echo '      Server Time: ' . date('d<\sup>S</\sup> F Y, G:i:s') . '<br />';
  echo '      Server Time - 3 hours: &nbsp;&nbsp;' . date('d<\sup>S</\sup> F Y, G:i:s', strtotime('-3 hours', time())) . '<br />';
?> 

Can you apply that too your code? Or something simmilar?

Live Example of above code: here.

Cheers,

Ryan Jones

[dotnotwhat]

The SQL query doesn't make use of your calculated local time.

The bit of code that works out your local time using an 8 hour offset seems fine, but you don't need the third line - it doesn't achieve anything here. You then need to use the $current_local_time in the date() function within your SQL statement.

Try this:-

$offset = -8*3600; // Local time offset (8 hours behind server time).
$current_local_time = time() + $offset; // UNIX timestamp of current local time.

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "insertForm"))
{
    $insertSQL = sprintf("INSERT INTO user_tbl (user_id, user_password, `role`, user_first_name, user_last_name, date_added) VALUES (%s, %s, %s, %s, %s, %s)",
    GetSQLValueString($_POST['user_id'], "text"),
    GetSQLValueString($_POST['password'], "text"),
    GetSQLValueString($_POST['role'], "int"),
    GetSQLValueString($_POST['first_name'], "text"),
    GetSQLValueString($_POST['last_name'], "text"),
    GetSQLValueString(date("Y-m-d H:i:s", $current_local_time), "date"));
}

[ChiChee]

Here's the code including the code immediatly preceeding the code you suggested I add. Can you help me determine where the parse error is coming from?

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;

case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}

$editFormAction = $SERVER['PHP_SELF'];
if (isset($SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

$offset = -8*3600;
$current_local_time = time() + $offset;

if ((isset($POST["MM_insert"])) && ($POST["MM_insert"] == "insertForm"))
{
$insertSQL = sprintf("INSERT INTO user_tbl (user_id, user_password, role, user_first_name, user_last_name, date_added) VALUES (%s, %s, %s, %s, %s, %s)",
GetSQLValueString($POST['user_id'], "text"),
GetSQLValueString($POST['password'], "text"),
GetSQLValueString($POST['role'], "int"),
GetSQLValueString($POST['first_name'], "text"),
GetSQLValueString($_POST['last_name'], "text"),
GetSQLValueString(date("Y-m-d H:i:s", $current_local_time), "date"));
}

mysql_select_db($database_test_data, $test_data);
$Result1 = mysql_query($insertSQL, $test_data) or die(mysql_error());

$insertGoTo = "userMaster.php";
if (isset($SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
?>

[dotnotwhat]

Do you get an error message? Were you getting this error before using my changes? I presumed your script was functional apart from the local date set in the database being wrong.

[ChiChee]

dotnotwhat wrote:

Do you get an error message?

Here is the error I receive:

Parse error: parse error, unexpected '}' in /home/test/public_html/php/userAdd.php on line 95

Line 95 points to : $editFormAction = $_SERVER['PHP_SELF'];
but I'm not certain that is the actual line that is the problem.

Were you getting this error before using my changes? I presumed your script was functional apart from the local date set in the database being wrong.

Yes, the code was fully functional and stored the server date/time just fine.

[dotnotwhat]

Where you have made my code changes, looks fine. Not sure what is causing the new error but I don't think it's anything to do with my suggestion, could you check that you havn't lost a { somewhere in your script?

There must be some of your script missing from that post, as $editFormAction = $_SERVER['PHP_SELF']; is not line 95. If you can't track down the missing { or extra } could you post the entire script?

A lot of IDEs will highlight syntax errors such as this in your code which may be of use long term.

[ChiChee]

A lot of IDEs will highlight syntax errors such as this in your code which may be of use long term.

It works now. 😃 I found the superfluous }. Once it was emoved, all was well.

Can you tell me what IDEs are? I'm using Dreamweaver 8 and it didn't highlight my error in any way. Should it be?

Thank you, thank you for your help!

[dotnotwhat]

Glad all your {s and }s add up 🙂

By IDE (Integrated Development Environment), I just meant any decent app to work with PHP development, etc. Most of the more popular ones will usually spot syntax errors as you code and highlight these errors in some way, making them easy to fix. I don't use Dreamweaver but I imagine that it has an option to do this.