[OFF][500 CREDS] Timezone issue

[freecrm]

After spending a week on this and not really getting anywhere, I need this fixed which is why I'm offering 500.

Platform: php on MySQL

Site: www.freecrm.x10hosting.com

Process for: Event management

AIM

1) User 1 in GB timezone (UTC+01:00) enters a date/time string, assisted by a JS datetime picker that returns a "Y-m-d H:i:s" format. I will call this value "exampledate"

The value is either inserted or updated to a table.

2) User 2 in US/Central timezone echo's the exampledate and sees the date relative to his timezone - i.e. -06:00 in a format which he/she has set in their preferences.

CURRENT VARIABLES & SETTINGS

Only users can enter dates adn each user can set their timezone and timeformat preferences which are loaded on login and carried as session variables

$_SESSION['MM_UTZ'] and $_SESSION['MM_UTF']

In each page header, I have the following include:

session_start();
//set page timezone
if ($_SESSION['MM_UTZ'] == NULL){
$_SESSION['MM_UTZ']="UTC";}
putenv ("TZ=".$_SESSION['MM_UTZ']);
//set page timeformat
if ($_SESSION['MM_UTF'] == NULL){
$_SESSION['MM_UTF']="Y-m-d H:i:s";
}

If there is no preference, it will set UTC and Y-m-d H:i:s as default values and assign $_SESSION['MM_UTZ'] to the default server timezone (I think... although I'm not clear if I should use the date_default_timezone_set() function).

THE INSERT PART

From a form field (which I shall call timefield), I am currently trying to convert the date/time string using new DateTime() to store.

$dto_timefield= new DateTime($_POST['timefield'], new DateTimeZone($_SESSION['MM_UTZ']));

I can then use this to insert

i.e. INSERT INTO EVENTS (EVSTART) VALUES ($dto_timefield->format(DATE_RFC3339))

If I echo this, it will return something like this.

08-09-02T22:07:31+01:00

I am then trying to store this is a VARCHAR MySQL field...

THE RESULTS PAGE

Not doing well here...

User 2 looks at this data in a different timezone from, say, a recordset.

How do I get this result to account for the difference in timezones??

strtotime??

mktime???

Another new DateTime object?


As you can see, I'm serious about getting this done so please - any help will be appreciated - even if you have a different approach.

[xPlozion]

if you've got the values for the timezone (UTC+1000 and UTC-5000 for example), then I've coded something real quick that could possibly help you out.

<?php
$old = $_GET['old']; // posted timezone
$new = $_GET['new']; // viewer's timezone
    $offset = $old-$new;
if ($old > $new) {
    echo "-".$offset;
} elseif ($old < $new) {
    echo str_replace("-", "+", $offset);
}
?>

That makes:
UTC+1000 posted UTC-5000 viewed to -6000
UTC-5000 posted UTC+1000 viewed to +6000
UTC+1000 posted UTC+6000 viewed to +5000
UTC+6000 posted UTC+1000 viewed to -5000

I don't know how much this can help you out, if at all...

-xP

[freecrm]

if you've got the values for the timezone (UTC+1000 and UTC-5000 for example), then I've coded something real quick that could possibly help you out.

<?php
$old = $_GET['old']; // posted timezone
$new = $_GET['new']; // viewer's timezone
    $offset = $old-$new;
if ($old > $new) {
    echo "-".$offset;
} elseif ($old < $new) {
    echo str_replace("-", "+", $offset);
}
?>

That makes:
UTC+1000 posted UTC-5000 viewed to -6000
UTC-5000 posted UTC+1000 viewed to +6000
UTC+1000 posted UTC+6000 viewed to +5000
UTC+6000 posted UTC+1000 viewed to -5000

I don't know how much this can help you out, if at all...

-xP

Thanks for the response...

In another forum, it has been suggested that I convert the date/time string from the form into a UTC epoch value as the environment settings will then return the UTC value correctly in user 2's timezone.

I'm not sure how to convert the date/time string into a default UTC format though!

Your post may be of some use further down the line...
Edit:
OK - this is doing my nut in - the offer has gone up to 800 credits!

Pretty soon, I'll be giving away my laptop!! :rant2:

[xPlozion]

I can use a laptop ;)

I found what you were looking for (I hope)

<?php
echo strtotime("08-09-02T22:07:31+01:00"); //In the format your JS d/t picker is ;) returns 1220389651
// check the output here: http://krijnen.com/time.php which prints
// M:09,D:02,Y:08,H:21,M:07,S:31 since your currently at UTC+1, to get to true UTC (UTC+0), you would subtract an hour

//now what you could use ;)
$time = strtotime($dto_timefield->format(DATE_RFC3339));
mysql_query("INSERT INTO events (evstart) VALUES ($time)");
?>

If there's any more questions, then please ask

-xP

[freecrm]

xPlozion - I have finally managed to sort it and you were not far off!!!

It's even simpler than you think.

Provided that you have set the default timezone from the user preferences using putenv("TZ".=$usertimezonepreference), you can very simply use the following:

$epoch_timefield=strtotime($_POST['dt_timefield']);

My datetime field is in YYYY-MM-DD HH:ii:ss format but you don't even need to worry about this!!

As you pointed out, the $epoch_timefield value can be inserted simply.

The strtotime full version is actually strtotime($time,$now) but the $now value defaults to the default environment now, which has been set in the users preference.... so you don't need it. The function then converts the $time value and stores as a UTC Epoch value.

When user 2 signs in, the new putenv() function reads the epoch UTC value in the database and echos according to that users timezone!!!


O....M.....G - so simple when you know how!

Seeing as you were pretty much there and, as I'm a man of my word - here's 800 creds!!!

[xPlozion]

Thank you, but I'd much rather have the laptop ;)

+REP
-xP