Tough Timezone Problem

[Google Mike]

I guess I'm confused by the whole timezone thing and I don't want to
write a web app that gets this stuff wrong. I don't know how timezones
work exactly -- is it just hours +/- on GMT or are there going to be
minor differences on minutes and seconds too?

What I want to do is have a web app that one logs into (great place for
submitting the browser's view on time via Javascript) and then the user
sees the records from the database with dates converted from GMT to
their local timezone. The database, however, will only store GMT time
for all the records. (Imagine a simplistic, one-table helpdesk
ticketing system for the style of database to make this easy. Oh, and
I'm more a fan of PostgreSQL than MySQL.)

How do I write such a thing? Let me know. Here's my rendition, if I
have this right.

If it's only just hours, then I guess I need my L1.php (login1) page --
which submits to L2.php for processing before showing Q.php for the
helpdesk queue -- to collect the browser's view on time via Javascript.
This is then stuck in a hidden field via Javascript and that field is
submitted to L2.php. L2 reads this and compares this with gmdate('Y-m-d
H:i:s'), checking only for the difference in hours. When it knows this,
then all the records (open tickets) it finds in the database have date
math applied to be this difference in hours. Even at that, I'm not too
skilled with applying date math in PHP. I didn't find a DateAdd or
DateDiff set of functions.

I'm also a Javascript Minimalist so that this doesn't break in certain
people's browsers. That's why all I was planning to do was collect the
browser's sense of date/time and pass that to L2.php so that the
processing can be consistent, rather than rely on Javascript to get it
right.

And then there's also the issue where you have a date in one timezone,
and the the previous or next day in the other timezone, so at that
point it becomes more than just hours, but changes in days too.

[Daniel Tryba]

Google Mike <go********@hotpop.com> wrote:

What I want to do is have a web app that one logs into (great place for
submitting the browser's view on time via Javascript) and then the user
sees the records from the database with dates converted from GMT to
their local timezone. The database, however, will only store GMT time
for all the records. (Imagine a simplistic, one-table helpdesk
ticketing system for the style of database to make this easy. Oh, and
I'm more a fan of PostgreSQL than MySQL.)

Take the KISS approach: leave _everything_ in GMT. That way you only
have to tell the user his offset to GMT or even more simply just tell
him the time in GMT when the page was loaded.

[Google Mike]

Yes, I have thought about that, and I do *LOVE* the KISS approach, and
I thank you for your extremely prompt reply (30 minutes is fantastic!).
However, I'm afraid my users might be too cranky to accept having to
translate GMT in their heads. People in different timezones (Brazil and
USA) will "live" in my web app all day, so they'll want it to be a
little more comfortable for them. Got another suggestion?

[ZeldorBlat]

After getting the time and date from the browser into PHP, I would
convert it to a timestamp and compare it with the timestamp from the
server -- that will tell you the difference in seconds, which solves
your problem of crossing dates. Check out the strtotime() function. I
would take this difference in seconds, divide by 3600 and round to the
nearest integer. That will tell you the difference in hours, and also
avoid cases where a couple minutes of seperation between clocks would
otherwise throw things off.

I don't know about Postgres, but in MySQL you can definitely retrieve
dates and times as UNIX timestamps directly from the database. With a
timestamp, making an adjustment is easy, because you just add or
subtract the correct number of seconds. Using PHP's date() function
will allow you to format a timestamp into something more readable.

As a side note, strtotime() will also let you do date math if you give
it a timestamp as your second argument (or relative to the current time
if you don't). For instance:

strtotime("+1 day");
strtotime("next Thursday");
strtotime("-5 hours");

[chotiwallah]

ZeldorBlat wrote:

After getting the time and date from the browser into PHP, I would
convert it to a timestamp and compare it with the timestamp from the
server -- that will tell you the difference in seconds, which solves
your problem of crossing dates. Check out the strtotime() function. I
would take this difference in seconds, divide by 3600 and round to the
nearest integer. That will tell you the difference in hours, and also
avoid cases where a couple minutes of seperation between clocks would
otherwise throw things off.
not that easy: india and nepal for instance have a 15 min difference in
their times (which is purely a political thing), and there might be
other countrys as well.

I don't know about Postgres, but in MySQL you can definitely retrieve
dates and times as UNIX timestamps directly from the database. With a
timestamp, making an adjustment is easy, because you just add or
subtract the correct number of seconds. Using PHP's date() function
will allow you to format a timestamp into something more readable.

As a side note, strtotime() will also let you do date math if you give
it a timestamp as your second argument (or relative to the current time
if you don't). For instance:

strtotime("+1 day");
strtotime("next Thursday");
strtotime("-5 hours");

[Daniel Tryba]

Google Mike <go********@hotpop.com> wrote:

Yes, I have thought about that, and I do *LOVE* the KISS approach, and
I thank you for your extremely prompt reply (30 minutes is
fantastic!). However, I'm afraid my users might be too cranky to
accept having to translate GMT in their heads. People in different
timezones (Brazil and USA) will "live" in my web app all day, so
they'll want it to be a little more comfortable for them. Got another
suggestion?

Translating timezones is extremly easy:
news:41***********************@dreader3.news.xs4al l.nl
(althought the IST timezone used there is BS, putenv is the answer to
your display question :)

[dracolytch]

You can use JavaScript, or ask the user for their current time. THe
difference between GMT and the given time is their time offset. I
store this in my user DB, but you could easily put it in a cookie or
somesuch. With that time offset, every time you display a date, be sure
to correct by their offset.

People really do appreciate being able to see all times local.

~D

[junk]

chotiwallah wrote:

not that easy: india and nepal for instance have a 15 min difference in
their times (which is purely a political thing), and there might be
other countrys as well.

Australia has 3 time zones in winter and up to 5 in summer. Tasmania,
Victoria, NSW, and Queensland are all in the same time zone. However
Tasmania changes to summer time a month before Vic, or NSW, while
Queensland does't use summer time. They also change back to standard
time on different dates.

[Google Mike]

I may have a routine below that works. It depends on the browser having
the proper representation of the time and all else falls into place. If
the browser doesn't have the proper representation of time, including
special summer/winter time adjustments and political time adjustments,
then that's a matter to complain to the browser vendor, in my opinion.
I mean, I can't work miracles here. (One suggestion is to always
provide a preferences setting in

<!-- login1 page (L1.php) -->
<html><head><title>Login p1</title>
<script language="javascript">
function GetTime() {
var now = new Date();
return now;
}
function UpdateHidden(MyForm) {
thedate = GetTime()
MyForm.fldUsertime.value=thedate;
return true;
}
</script></head><body>
<form name="main" id="main" method="post" action="L2.php"
onSubmit="javascript:UpdateHidden(this)">
<p>The date in your locale is:
<script language="javascript">
document.write(GetTime());
</script>
</p><input type=hidden name="fldUsertime" id="fldUsertime" value="">
<input type="submit" value="Login"></form></body></html>

<!-- login2 page (L2.php) -->
<!-- THIS IS SUPPOSED TO BE A SEPARATE FILE (L2.php) -->
<?php
function GetLocalTime($dBrowserDateTime) {
return date('Y-m-d H:i:s', time() + strtotime($dBrowserDateTime));
}
function GetTimezoneOffset($dUserDateTime) {
return (strtotime($dUserDateTime)-strtotime(gmdate('Y-m-d
H:i:s')))/3600;
}
function TranslateByTimezoneOffset($dDateTime,$nTZO) {
return date('Y-m-d H:i:s',strtotime($dDateTime)+($nTZO*3600));
}
//sub main
$dUsertime=$_POST['fldUsertime'];
$dUsertime=GetLocalTime($dUsertime);
$nTZO = GetTimezoneOffset($dUsertime);
$dTest = '2005-01-28 07:15:00';
$dResult = TranslateByTimezoneOffset($dTest,$nTZO);
$dGMT = gmdate('Y-m-d H:i:s');
echo "Your browser tells me the time is: $dUsertime<BR>\n";
echo "And the web server's concept of GMT is: $dGMT<BR>\n";
echo "Your Timezone Offset is: $nTZO<BR>\n";
echo "It would be a good idea to store the Timezone Offset in a
cookie.<BR>\n";
echo "<P>So if I had a GMT date in the database that was
<B>$dTest</B>,&nbsp;";
echo "you would see it as <B>$dResult</B> if you used the&nbsp;";
echo "TranslateByTimezoneOffset() routine and passed it the Timezone
Offset&nbsp;";
echo "cookie value.</P>\n";
echo "<A HREF='javascript:history.back()'>Go Back</A>";
?>
<html><head><title>Login p2</title></head><body></body></html>

[Google Mike]

Got cutoff there when I wanted to finish the sentence for "(One
suggestion is to always provide a preferences setting in..."

My suggestion is that one could use the above routine, but then always
provide an override setting where one can change their +/- setting on
the GMT with a SELECT/OPTION field and store that in a persistent
cookie.