Thread: Determine Time in other Time Zone
I need to determine the users time in another Time Zone for the time now(). I tried to manually add or subtract the difference of hours between the server timezone and the other timezone, but I have to do it for _Every_ timezone. Not only that, but I need to take Daylight Saving Time (DST) into account for those timezones that use it. I have tried the timezone(text,timestamp) function, but for that to work with daylight savings time I need to know whether the DST is being applied for this date. Ie I have to give the function either MST or MDT, and I don't know how to switch it based on now() and the timezone. I know that I can set the postgres timezone to a timezone defined within Linux's /usr/share/zoneinfo, and that adjusts the local time properly including DST. What I want is a way to ask for the time in a different time zone and use the Linux zoneinfo to calculate it. Does anyone know have an idea how I can solve this problem? Would anyone else be able to use this functionality? Marc Wrubleski Sorex Software Inc. Calgary, Alberta, Canada 1 (403) 371-7598
On Fri, Mar 09, 2001 at 07:18:00PM -0700, Marc Wrubleski wrote: > I need to determine the users time in another Time Zone for the > time now(). > > I tried to manually add or subtract the difference of hours > between the server timezone and the other timezone, but I have > to do it for _Every_ timezone. Not only that, but I need to > take Daylight Saving Time (DST) into account for those > timezones that use it. > > I have tried the timezone(text,timestamp) function, but for > that to work with daylight savings time I need to know whether > the DST is being applied for this date. Ie I have to give the > function either MST or MDT, and I don't know how to switch it > based on now() and the timezone. > > I know that I can set the postgres timezone to a timezone > defined within Linux's /usr/share/zoneinfo, and that adjusts > the local time properly including DST. > > What I want is a way to ask for the time in a different time > zone and use the Linux zoneinfo to calculate it. > > Does anyone know have an idea how I can solve this problem? > Would anyone else be able to use this functionality? did you ever get a response on this? it looks interesting and useful... -- will@serensoft.com http://newbieDoc.sourceforge.net/ -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
I don't think I've got anything useful to add except maybe to offer some thoughts. First, do your users use postgres from their machine via ssh? The Web? Some other way? If it's from the web, perhaps you could include some code about localtime and have that sent to your server when people log in. If it's via ssh I don't know. Maybe you could write a short script that would poll the remote machine's timezone data...(<major shrug> I'm an amateur! :-) Second, perhaps this sort of problem has been solved via the ntp software? ntp polls various time servers. Perhaps there is a way to call a timeserver that is local to where ever the users are logging in from. Third, so far I haven't mentioned postgresql in any of this. But it sounds like it's just a matter of taking externel information and making it internal... Oh well, these are just my thoughts! Russell ____________________________________________________ _its_ (no apostrophe) means "the thing that it owns" _it's_ (with apostrophe) means "it is" ---------- >From: will trillich <will@serensoft.com> >To: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Determine Time in other Time Zone >Date: Tue, Mar 27, 2001, 1:15 AM > > On Fri, Mar 09, 2001 at 07:18:00PM -0700, Marc Wrubleski wrote: >> I need to determine the users time in another Time Zone for the >> time now(). >> >> I tried to manually add or subtract the difference of hours >> between the server timezone and the other timezone, but I have >> to do it for _Every_ timezone. Not only that, but I need to >> take Daylight Saving Time (DST) into account for those >> timezones that use it. >> >> I have tried the timezone(text,timestamp) function, but for >> that to work with daylight savings time I need to know whether >> the DST is being applied for this date. Ie I have to give the >> function either MST or MDT, and I don't know how to switch it >> based on now() and the timezone. >> >> I know that I can set the postgres timezone to a timezone >> defined within Linux's /usr/share/zoneinfo, and that adjusts >> the local time properly including DST. >> >> What I want is a way to ask for the time in a different time >> zone and use the Linux zoneinfo to calculate it. >> >> Does anyone know have an idea how I can solve this problem? >> Would anyone else be able to use this functionality? > > did you ever get a response on this? it looks interesting and > useful... > > -- > will@serensoft.com > http://newbieDoc.sourceforge.net/ -- we need your brain! > http://www.dontUthink.com/ -- your brain needs us! > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
"Russell Hires" <rhires@earthlink.net> writes: > Second, perhaps this sort of problem has been solved via the ntp software? > ntp polls various time servers. Perhaps there is a way to call a timeserver > that is local to where ever the users are logging in from. NTP keeps time in UTC. Translation into local time is the responsibility of the client. So that won't help much. Translating times between time zones is a hard problem. One thing to look at is the timezone code that is shipped with free Unices (BSD/Linux) as it comes with a database of timezones with their offsets and DST rules. -Doug
Doug McNaught wrote:
>
> "Russell Hires" <rhires@earthlink.net> writes:
>
> > Second, perhaps this sort of problem has been solved via the ntp software?
> > ntp polls various time servers. Perhaps there is a way to call a timeserver
> > that is local to where ever the users are logging in from.
>
> NTP keeps time in UTC. Translation into local time is the
> responsibility of the client. So that won't help much.
>
> Translating times between time zones is a hard problem. One thing to
> look at is the timezone code that is shipped with free Unices
> (BSD/Linux) as it comes with a database of timezones with their
> offsets and DST rules.
it SHOULD be simple, if you have access to what you need:
-- convert originaltime to localtime:
localTime = originalTime - originalTimeZone + localTimeZone
isn't there any quickie utility to deliver such variables? maybe at the
shell/scripting level? this is *nix, after all...
--
mailto:will@serensoft.com
http://www.dontUthink.com/
Hi Will, I am trying to get the time in another timezone as well, and I have it pinned down in Linux as to a couple ways to do it. The first is to write c-code that sets a TZ environment variable to the desired time zone, gets the time (via ctime or localtime) and then switches the TZ environment variable back. This is NOT efficient as you can imagine, but it works, and returns accurate values (even accounting for Daylight Saving Time I believe). The other option I will be looking into is to use the tzfile functions within linux to read the desired time zone information and to calculate the time zone's time based on the results from tzfile. tzfile is the program that reads the time zone information from the /usr/share/zoneinfo files. This would be somewhat more efficient, but either way, when you are reading the time zone information from the disk, it won't be very fast. Hope it helps. Marc Wrubleski will trillich wrote: > Doug McNaught wrote: > > > > "Russell Hires" <rhires@earthlink.net> writes: > > > > > Second, perhaps this sort of problem has been solved via the ntp software? > > > ntp polls various time servers. Perhaps there is a way to call a timeserver > > > that is local to where ever the users are logging in from. > > > > NTP keeps time in UTC. Translation into local time is the > > responsibility of the client. So that won't help much. > > > > Translating times between time zones is a hard problem. One thing to > > look at is the timezone code that is shipped with free Unices > > (BSD/Linux) as it comes with a database of timezones with their > > offsets and DST rules. > > it SHOULD be simple, if you have access to what you need: > > -- convert originaltime to localtime: > localTime = originalTime - originalTimeZone + localTimeZone > > isn't there any quickie utility to deliver such variables? maybe at the > shell/scripting level? this is *nix, after all... > > -- > mailto:will@serensoft.com > http://www.dontUthink.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Upon further review... :-) I had a look at the ntp website, and there is a bunch of links at the bottom. http://www.eecis.udel.edu/~ntp/ is the main ntp page. http://www.bsdi.com/date/date?PRC is pretty interesting. It appears that someone has already figured out the time in the various timezones! How you could link this into a database I don't know...Maybe you could add an entry for each user that would incorporate their location based on the list on that web page. I then went a-lookin' for more info on this. www.timezoneconverter.com was looking interesting. It contains cgi-bin info (unforunately, hidden from view). There was also a list of routines at ftp://elsie.nci.nih.gov/pub/ and instructions at http://sandbox.xerox.com/stewart/tzconvert.cgi/info#help Okay, enough research (Phew! I'm tired...). Is any of this useful? How could it be incorporated into PSQL? I assume it's possible to call outside routines from PSQL. Hope this helps, Russell ____________________________________________________ _its_ (no apostrophe) means "the thing that it owns" _it's_ (with apostrophe) means "it is" ---------- >From: Doug McNaught <doug@wireboard.com> >To: "Russell Hires" <rhires@earthlink.net> >Cc: will trillich <will@serensoft.com>, pgsql-general@postgresql.org >Subject: Re: [GENERAL] Determine Time in other Time Zone >Date: Wed, Mar 28, 2001, 10:12 PM > > "Russell Hires" <rhires@earthlink.net> writes: > >> Second, perhaps this sort of problem has been solved via the ntp software? >> ntp polls various time servers. Perhaps there is a way to call a timeserver >> that is local to where ever the users are logging in from. > > NTP keeps time in UTC. Translation into local time is the > responsibility of the client. So that won't help much. > > Translating times between time zones is a hard problem. One thing to > look at is the timezone code that is shipped with free Unices > (BSD/Linux) as it comes with a database of timezones with their > offsets and DST rules. > > -Doug > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl