Time Zone design issues

novnov <novnovice@gmail.com> writes:

At this time my app will be hosted on a server at a single location. Users
will be updating and viewing from various time zones. I would like to
present the datetime of the last update to each user according to the time
zone that they've entered into their profile (stored in the same app db).

This is trivial. The column should be timestamptz, and you set the
PG timezone parameter to the user's preferred zone within each user's
session.

Under the hood, the data stored in the timestamptz column is "really"
UTC, and conversion to and from the user's preferred zone happens
automatically when the value is input or output.

You should absolutely not try to fake this by skewing the server's
clock, nor by doing "manual" timezone conversions --- any such hack
will lead to great pain.

What I am not so sure of is how to coordinate the timezones. It seems like a
complex subject. Most of the timezone data lists include all timezones, with
all dst variations.

Yeah, there are a lot of 'em :-( Feel free to filter the set of timezone
names you make visible to your users, if you have a good idea which ones
are really likely to be interesting to them. There was some discussion
of that a couple weeks ago IIRC.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/09/07 22:29, novnov wrote:
[snip]

But it doesn't particularly make sense to set the server's clock to UTC.
It'd seem to skew a lot of server functionality which I'd think should
normally be geared around local time. So I'd guess that the route to take is
to keep the server pegged to local time, and use a function that derives UTC
from now(), tags the 'last modified' fields with that value.

Your single-user Windows mindset is shining brightly.

Unix servers have had their internal clocks set to UTC for a decade
or more, and there have been no noticeable ill effects, since apps
all know to adjust for TZ.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5MfXS9HxQb37XmcRAuf5AKDKm9h0AxznSTJ0fJx7KzVqFDblYACfeSUV
Lub89IZdWSIfvGhUZde/jG0=
=3+7a
-----END PGP SIGNATURE-----

Ha ha that's a funny and accurate way of putting it! I am deploying on a
linux box, so it's good to know this.

But 'apps all know to adjust for TZ'; really? In this case I'm creating the
app, I can't imagine that it will automatically know to adjust for TZ? The
postgres function now() would output now as far as the server is concerned
(per it's local time setting I suppose), but very surprising is there is
enough magic to report to user time as adjusted to their time zone as
recorded in the db?

Ron Johnson wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/09/07 22:29, novnov wrote:
[snip]

But it doesn't particularly make sense to set the server's clock to UTC.
It'd seem to skew a lot of server functionality which I'd think should
normally be geared around local time. So I'd guess that the route to take
is
to keep the server pegged to local time, and use a function that derives
UTC
from now(), tags the 'last modified' fields with that value.

Your single-user Windows mindset is shining brightly.

Unix servers have had their internal clocks set to UTC for a decade
or more, and there have been no noticeable ill effects, since apps
all know to adjust for TZ.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5MfXS9HxQb37XmcRAuf5AKDKm9h0AxznSTJ0fJx7KzVqFDblYACfeSUV
Lub89IZdWSIfvGhUZde/jG0=
=3+7a
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12594326
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Sep 10, 2007 at 07:46:17AM -0700, novnov wrote:

But 'apps all know to adjust for TZ'; really? In this case I'm creating the
app, I can't imagine that it will automatically know to adjust for TZ? The
postgres function now() would output now as far as the server is concerned
(per it's local time setting I suppose), but very surprising is there is
enough magic to report to user time as adjusted to their time zone as
recorded in the db?

Look up the definition of the time() function. In UNIX time is measured
in seconds since 1 Jan 1970 UTC. This is a number that is obvisouly the
same no matter where you are in the world. The C library has a function
called localtime() that does all the magic of converting to whatever
you consider local time.

The postgres function now() is the same: it returns a number of seconds.
Only when you actually go to display it does it do the localtime()
dance (internally) to make it look nice. That why you can set the
timezone to whatever you like and everything works the same.

I can log into a number of servers in the world and I just have it
setup to export TZ="my timezone" and everything is in my timezone, no
matter where the machine is or whatever anyone else on the machine is
doing. It's not magic, just someone 30 years ago making the smart
choice.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Yes, but there are still some parts of this I don't understand. From the
application user's perspective, the datetime a record was last updated needs
to be presented in their local time, in the browser. I have never been
intimate with web apps that accomodate user profile time settings, but have
used many. They let you set your local timezone. Else the app presents UTC.
So, something internal to that app (joomla, drupal etc) must be optionally
adjusting datetimes presented to the users. That's what I was suggesting; I
think your reqly and the one prior to it suggest that these things are
somehow 'handled'. Tom Lane seemed to point to a param to use with tz and
I'm trying to figure that out.

As far as I can see, my original premis is correct, that I need to tweak the
stored datetime when returned for viewing in a browser to adjust for any
user's tz setting...I'd be wrong if browsers automatically adjust any
datetime for the requesting pc's tz setting. I don't think they do, else why
would web apps like joomla and drupal adjust the datetime presented to the
end user according to whatever they set at their tz?

Your explanation of now() and localtime() is good...but localtime() to
postgres acting as a web app db, wouldn't it just return the local time as
far as the server is concerned?

Martijn van Oosterhout wrote:

On Mon, Sep 10, 2007 at 07:46:17AM -0700, novnov wrote:

But 'apps all know to adjust for TZ'; really? In this case I'm creating
the
app, I can't imagine that it will automatically know to adjust for TZ?
The
postgres function now() would output now as far as the server is
concerned
(per it's local time setting I suppose), but very surprising is there is
enough magic to report to user time as adjusted to their time zone as
recorded in the db?

Look up the definition of the time() function. In UNIX time is measured
in seconds since 1 Jan 1970 UTC. This is a number that is obvisouly the
same no matter where you are in the world. The C library has a function
called localtime() that does all the magic of converting to whatever
you consider local time.

The postgres function now() is the same: it returns a number of seconds.
Only when you actually go to display it does it do the localtime()
dance (internally) to make it look nice. That why you can set the
timezone to whatever you like and everything works the same.

I can log into a number of servers in the world and I just have it
setup to export TZ="my timezone" and everything is in my timezone, no
matter where the machine is or whatever anyone else on the machine is
doing. It's not magic, just someone 30 years ago making the smart
choice.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

From each according to his ability. To each according to his ability to
litigate.

--
View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12595616
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Martijn van Oosterhout escribi�:

The postgres function now() is the same: it returns a number of seconds.
Only when you actually go to display it does it do the localtime()
dance (internally) to make it look nice. That why you can set the
timezone to whatever you like and everything works the same.

FWIW, the equivalent to the TZ variable in Postgres is setting the
TimeZone variable (either using SET at the start of the session, or via
ALTER USER/SET, etc).

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"E pur si muove" (Galileo Galilei)

novnov wrote:

As far as I can see, my original premis is correct, that I need to tweak the
stored datetime when returned for viewing in a browser to adjust for any
user's tz setting...I'd be wrong if browsers automatically adjust any
datetime for the requesting pc's tz setting. I don't think they do, else why
would web apps like joomla and drupal adjust the datetime presented to the
end user according to whatever they set at their tz?

Your explanation of now() and localtime() is good...but localtime() to
postgres acting as a web app db, wouldn't it just return the local time as
far as the server is concerned?

To be precise, it's as far as the *client library* (e.g. DBD::Pg in
Perl, or your pg_xxx functions in PHP) is concerned.

=> show timezone;
TimeZone
----------
GB
(1 row)

=> select now();
now
-------------------------------
2007-09-10 16:46:51.275526+01
(1 row)

=> SET timezone='EST';
SET
=> select now();
now
-------------------------------
2007-09-10 10:46:51.275526-05
(1 row)

So you'll want to issue the correct "SET timezone" for each user, but
after that it's all handled for you. If the application wasn't web-based
you'd not need to keep re-connecting so you'd only have to do it once.

--
Richard Huxton
Archonet Ltd

On Mon, Sep 10, 2007 at 08:34:55AM -0700, novnov wrote:

Your explanation of now() and localtime() is good...but localtime() to
postgres acting as a web app db, wouldn't it just return the local time as
far as the server is concerned?

It will return localtime relative to whatever you've configured the
timezone to be. Have your webapp execute "set timezone = 'foo'" at the
beginning of the session and everything will flow from there.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On 9/10/07, novnov <novnovice@gmail.com> wrote:

Your explanation of now() and localtime() is good...but localtime() to
postgres acting as a web app db, wouldn't it just return the local time as
far as the server is concerned?

No, it would return it as the local time of the POSTGRESQL client.

For instance, suppose that someone in NY, connects to a web app, which
asks him what TZ he's in and he picks EST5EDT. The web app sets
timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'.

The database now stores that as '2007-07-11 16:30:00+00'

No matter WHERE the postgresql server is, that's what it has in it.

Now, I connect from Chicago, and your web app ascertains my timezone
as CST6CDT. When I ask for the same timestamp, I get '2007-07-11
11:30:00-05'.

Now, if you don't set a timezone, then the database will use it's own.
If your postgresql server is in california, then it might have a
timezone of PST8PDT set. If your web app didn't tell it otherwise,
then the time would be '2007-07-11 09:30:00-07'.

Note that there are IP lookup tools you can use to determine, or at
least make a wild guess at, someone's timezone. But you still need to
let them pick one if you get it wrong. Then you can store that
timezone in a user profile and set it everytime the user uses your web
app.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/10/07 10:55, Scott Marlowe wrote:

On 9/10/07, novnov <novnovice@gmail.com> wrote:

Your explanation of now() and localtime() is good...but localtime() to
postgres acting as a web app db, wouldn't it just return the local time as
far as the server is concerned?

No, it would return it as the local time of the POSTGRESQL client.

For instance, suppose that someone in NY, connects to a web app, which
asks him what TZ he's in and he picks EST5EDT. The web app sets
timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'.

The database now stores that as '2007-07-11 16:30:00+00'

No matter WHERE the postgresql server is, that's what it has in it.

Now, I connect from Chicago, and your web app ascertains my timezone
as CST6CDT. When I ask for the same timestamp, I get '2007-07-11
11:30:00-05'.

Now, if you don't set a timezone, then the database will use it's own.
If your postgresql server is in california, then it might have a
timezone of PST8PDT set. If your web app didn't tell it otherwise,
then the time would be '2007-07-11 09:30:00-07'.

Note that there are IP lookup tools you can use to determine, or at
least make a wild guess at, someone's timezone. But you still need to
let them pick one if you get it wrong. Then you can store that
timezone in a user profile and set it everytime the user uses your web
app.

In both Windows *and* Unix "you" set your TZ when you install the
system. There are system functions to inquire how you've set it.

Browsers already report back a mountain of client data to the web
server. I'd be stunned if FF, IE, Opera, Konq, etc don't already
expose TZ, too.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5W04S9HxQb37XmcRAhsrAKCr3WQZi0oyWSJsMxortjoExeaS1QCg5HbS
G+fd0X7UvX9406A+Td2GYpw=
=wk17
-----END PGP SIGNATURE-----

I think I get the picture; this post is the closest to making sense to me (my
lack of understanding is the issue, obviously). But:

What's the postgresql client, in a web app?

When you write "The web app sets timezone='EST5EDT' and inserts a time of
'2007-07-11 12:30:00'." that's the black box that I'm asking about. There is
no web app other than the one I am writing. So, I think it means I need to
write code in my web app to handle the conversion of the postgres stored UTC
into user-relevant time, with their local time zone etc. If that's so, what
I'm asking about is one the webapp side and perhaps people here are not so
used to dealing with that. I don't know. Just like one of the other (much
appreciated) responders in this thread suggested, the user's browser is
likely offering the user's tz info...but I am interested in seeing someone's
mature system for returning the user's local time based their tz as stored
in their profile.

But since no one has agreed with my positing of that approach so far I'm not
sure. I'll see what I can figure out with a bit of testing etc.

Thanks everyone.

Scott Marlowe-2 wrote:

On 9/10/07, novnov <novnovice@gmail.com> wrote:

Your explanation of now() and localtime() is good...but localtime() to
postgres acting as a web app db, wouldn't it just return the local time
as
far as the server is concerned?

No, it would return it as the local time of the POSTGRESQL client.

For instance, suppose that someone in NY, connects to a web app, which
asks him what TZ he's in and he picks EST5EDT. The web app sets
timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'.

The database now stores that as '2007-07-11 16:30:00+00'

No matter WHERE the postgresql server is, that's what it has in it.

Now, I connect from Chicago, and your web app ascertains my timezone
as CST6CDT. When I ask for the same timestamp, I get '2007-07-11
11:30:00-05'.

Now, if you don't set a timezone, then the database will use it's own.
If your postgresql server is in california, then it might have a
timezone of PST8PDT set. If your web app didn't tell it otherwise,
then the time would be '2007-07-11 09:30:00-07'.

Note that there are IP lookup tools you can use to determine, or at
least make a wild guess at, someone's timezone. But you still need to
let them pick one if you get it wrong. Then you can store that
timezone in a user profile and set it everytime the user uses your web
app.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12601031
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Sep 10, 2007 at 12:44:04PM -0700, novnov wrote:

What's the postgresql client, in a web app?

Your webapp *is* the postgresql client. The client is whatever opens
the connection to the server.

When you write "The web app sets timezone='EST5EDT' and inserts a time of
'2007-07-11 12:30:00'." that's the black box that I'm asking about. There is
no web app other than the one I am writing. So, I think it means I need to
write code in my web app to handle the conversion of the postgres stored UTC
into user-relevant time, with their local time zone etc.

No, you really just send the query "set timezone ='EST5EDT'". You don't
have to code anything special at all. Have you tried this? Just go into
the psql prompt, add some data and switch the timezones around to see
what happens. Your app doesn't need to know anything about the
timezones at all...

If that's so, what
I'm asking about is one the webapp side and perhaps people here are not so
used to dealing with that. I don't know. Just like one of the other (much
appreciated) responders in this thread suggested, the user's browser is
likely offering the user's tz info...but I am interested in seeing someone's
mature system for returning the user's local time based their tz as stored
in their profile.

The user's browser doesn't send the timezone (probably because there's
no standard way of specifying it). However there is code on the web to
give you a guess though, by using javascript to get time difference
from UTC.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

novnov escribi�:

I think I get the picture; this post is the closest to making sense to me (my
lack of understanding is the issue, obviously). But:

What's the postgresql client, in a web app?

When you write "The web app sets timezone='EST5EDT' and inserts a time of
'2007-07-11 12:30:00'." that's the black box that I'm asking about. There is
no web app other than the one I am writing. So, I think it means I need to
write code in my web app to handle the conversion of the postgres stored UTC
into user-relevant time, with their local time zone etc.

You are misunderstanding the "sets timezone=". What it means is that
you call the following SQL command:
SET timezone TO 'EST5EDT';
early in your webapp code. The value to use, you get from the user
profile (stored in a table perhaps).

If that's so, what I'm asking about is one the webapp side and perhaps
people here are not so used to dealing with that. I don't know. Just
like one of the other (much appreciated) responders in this thread
suggested, the user's browser is likely offering the user's tz
info...but I am interested in seeing someone's mature system for
returning the user's local time based their tz as stored in their
profile.

I wouldn't trust the browser's TZ, and you would need a way to override
it. So storing it in a table seems the easiest way.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/10/07 15:21, Alvaro Herrera wrote:
[snip]

I wouldn't trust the browser's TZ, and you would need a way to
override it.

Why?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5asLS9HxQb37XmcRAoQqAKCoqdRr6cIOnIktIa8l2689isYtAQCbBK9z
/O1wYFZrtlols1lrvo1Rw5Q=
=WcJ6
-----END PGP SIGNATURE-----

Ron Johnson <ron.l.johnson@cox.net> writes:

On 09/10/07 15:21, Alvaro Herrera wrote:

I wouldn't trust the browser's TZ, and you would need a way to
override it.

Why?

The browser may not know the setting, or may not tell it to you,
or you might not be able to make any sense of what it says
(timezone names are hardly standardized).

This whole sub-thread actually is predicated on an assumption not
in evidence, which is that there is any browser anywhere that will
tell the http server timezone information. I'm quite sure no such
thing is required by the http standard.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/10/07 19:50, Tom Lane wrote:

Ron Johnson <ron.l.johnson@cox.net> writes:

On 09/10/07 15:21, Alvaro Herrera wrote:

I wouldn't trust the browser's TZ, and you would need a way to
override it.

Why?

The browser may not know the setting, or may not tell it to you,
or you might not be able to make any sense of what it says
(timezone names are hardly standardized).

Well that's true. Except for numeric offsets.

This whole sub-thread actually is predicated on an assumption not
in evidence, which is that there is any browser anywhere that will
tell the http server timezone information. I'm quite sure no such
thing is required by the http standard.

I'm really surprised.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5ff8S9HxQb37XmcRAstvAJ4tnhHhv9SCWnrWGq8td5orPttrfgCg1c+t
IyhqUpzNg6RDS3wkALx5mUc=
=BtHs
-----END PGP SIGNATURE-----

The browser may not know the setting, or may not tell it to you,
or you might not be able to make any sense of what it says
(timezone names are hardly standardized).

Well that's true. Except for numeric offsets.

Offsets aren't good enough due to changing DST rules. A current
offset may not be the right one for a stamp from 6 hours ago, and the
DST changeover point may be different for a stamp from a year ago.

"Ron Johnson" <ron.l.johnson@cox.net> writes:

On 09/10/07 19:50, Tom Lane wrote:

This whole sub-thread actually is predicated on an assumption not
in evidence, which is that there is any browser anywhere that will
tell the http server timezone information. I'm quite sure no such
thing is required by the http standard.

I'm really surprised.

I think all you get is the localized language. If it's localized to a
particular country then that might be good enough for a guess from some
countries but there's not much you can do with en_US or ru_RU.

I think most big commercial sites that decide they need this just buy access
to one of the ip to geographic location services which are far from perfect
but in my experience are plenty good enough to get a reasonable time zone.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Sep 11, 2007, at 2:48 AM, Gregory Stark wrote:

"Ron Johnson" <ron.l.johnson@cox.net> writes:

On 09/10/07 19:50, Tom Lane wrote:

This whole sub-thread actually is predicated on an assumption not
in evidence, which is that there is any browser anywhere that will
tell the http server timezone information. I'm quite sure no such
thing is required by the http standard.

I'm really surprised.

I think all you get is the localized language. If it's localized to a
particular country then that might be good enough for a guess from
some
countries but there's not much you can do with en_US or ru_RU.

I think most big commercial sites that decide they need this just
buy access
to one of the ip to geographic location services which are far from
perfect
but in my experience are plenty good enough to get a reasonable
time zone.

Or, more likely, use one of several approaches to either get the
timezone from the browser or get the browsers view of localtime
and do a little math on the server. Javascript, mostly.

(Though, AIUI, if you're using Javascript the elegant trick is to send
UTC qpoch time to the browser and have it do the rendering to the
local timezone
anyway).

Cheers,
Steve