Обсуждение: Retrieve the server's time zone
Hello, it seems the JDBC driver (or Java?) makes it impossible to retrieve the server's time zone. Apparently the driver changes the timezone setting so that it can't be reset to the original from the server. When I run "show timezone" from within JDBC I *always* get the client's time zone, even if I do a "reset timezone" beforethat When I do the same with psql I can see the server's time zone. Ultimately I would like to get the server's time, e.g. by running something like: select current_timestamp at time zone server_timezone I already posted this to the general mailing list, because I initially thought this was a Postgres problem. Tom Lane suggested, I could see the original time zone the server is set up with using: select reset_val from pg_settings where name = 'TimeZone'; But that again returns the client's time zone through JDBC, but the real time zone through psql. It seems the driver changes this setting permanently for the session. If that is the case, can I prevent that somehow? Thomas
It is in the session startup messages. I'm trying to recall the technical details as to why. My recollection is that for timestamps without timezone will be reported in the client timezone.
Do you want the server timezone or postgres timezone. A simple C function should provide the latter.
On 21 November 2017 at 02:01, Thomas Kellerer <spam_eater@gmx.net> wrote:
Hello,
it seems the JDBC driver (or Java?) makes it impossible to retrieve the server's time zone.
Apparently the driver changes the timezone setting so that it can't be reset to the original from the server.
When I run "show timezone" from within JDBC I *always* get the client's time zone, even if I do a "reset timezone" before that
When I do the same with psql I can see the server's time zone.
Ultimately I would like to get the server's time, e.g. by running something like:
select current_timestamp at time zone server_timezone
I already posted this to the general mailing list, because I initially thought this was a Postgres problem.
Tom Lane suggested, I could see the original time zone the server is set up with using:
select reset_val from pg_settings where name = 'TimeZone';
But that again returns the client's time zone through JDBC, but the real time zone through psql.
It seems the driver changes this setting permanently for the session.
If that is the case, can I prevent that somehow?
Thomas
> > From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer > Sent: Dienstag, 21. November 2017 12:30 > To: Thomas Kellerer <spam_eater@gmx.net> > Cc: List <pgsql-jdbc@postgresql.org> > Subject: Re: Retrieve the server's time zone > > It is in the session startup messages. I'm trying to recall the technical details as to why. My recollection is that fortimestamps without timezone will be reported in the client timezone. > > Do you want the server timezone or postgres timezone. A simple C function should provide the latter. Hello, As a workaround, you could add your own GUC variable "myServerTimeZone" regards, Marc Mamin > > Dave Cramer > > davec@postgresintl.com > www.postgresintl.com > >> On 21 November 2017 at 02:01, Thomas Kellerer <spam_eater@gmx.net> wrote: >> Hello, >> >> it seems the JDBC driver (or Java?) makes it impossible to retrieve the server's time zone. >> >> Apparently the driver changes the timezone setting so that it can't be reset to the original from the server. >> >> When I run "show timezone" from within JDBC I *always* get the client's time zone, even if I do a "reset timezone" beforethat >> >> When I do the same with psql I can see the server's time zone. >> >> Ultimately I would like to get the server's time, e.g. by running something like: >> >> select current_timestamp at time zone server_timezone >> >> I already posted this to the general mailing list, because I initially thought this was a Postgres problem. >> Tom Lane suggested, I could see the original time zone the server is set up with using: >> >> select reset_val from pg_settings where name = 'TimeZone'; >> >> But that again returns the client's time zone through JDBC, but the real time zone through psql. >> >> It seems the driver changes this setting permanently for the session. >> If that is the case, can I prevent that somehow? >> >> Thomas >> > >
Hi,
Could you please clarify what is the case of "receiving server-side time zone"?
For instance: do you need PostgreSQL time zone or OS time zone?
Vladimir
Vladimir Sitnikov schrieb am 21.11.2017 um 12:52: > Could you please clarify what is the case of "receiving server-side time zone"? > > For instance: do you need PostgreSQL time zone or OS time zone? I need a timestamp in the server's time zone. Or at leas the time zone of the OS. The one that is configured for Postgres on the server would do as well (in my case that is the server's OS time zone) E.g. select localtimestamp will return the server's local time converted to the *client's* time zone in JDBC. In psql however, it does return the timestamp as "seen" by the server. I could live with getting the server's OS time zone through "show timezone" because then I could convert current_timestamp back to that time zone. Thomas
>I need a timestamp in the server's time zone
What are you going to use it for?
Frankly speaking, I don't think application should depend on PostgreSQL / OS time zones.
Vladimir
Hello,
it seems the JDBC driver (or Java?) makes it impossible to retrieve the server's time zone.
Apparently the driver changes the timezone setting so that it can't be reset to the original from the server.
Yes, this is the case. No, in the current code there is no means to prevent it.
psql doesn't provide object instantiation on the results being passed back from the server: all it does is print stuff to the screen. That JDBC and psql different in their behavior with respect to timestamps/dates is unsurprising in that light. I don't know how libpq-based C (or other) language programs operate within this area but that would be the proper API to compare JDBC to.
As a work-around have your application require psql on the client machine and shell out to it. Or hack the driver - though using your timezone-less connection for anything other than finding out the server timezone is probably something you'd want to avoid.
David J.
On Tue, 2017-11-21 at 13:44 +0100, Thomas Kellerer wrote: > Vladimir Sitnikov schrieb am 21.11.2017 um 12:52: > > Could you please clarify what is the case of "receiving server-side > > time zone"? > > > > For instance: do you need PostgreSQL time zone or OS time zone? > > I need a timestamp in the server's time zone. Or at leas the time > zone of > the OS. The one that is configured for Postgres on the server would > do as > well (in my case that is the server's OS time zone) > > E.g. > > select localtimestamp > > will return the server's local time converted to the *client's* time > zone in JDBC. > > In psql however, it does return the timestamp as "seen" by the > server. > > I could live with getting the server's OS time zone through "show > timezone" > because then I could convert current_timestamp back to that time > zone. > > Thomas > > > > > Hi Thomas, What does java.util.TimeZone.getDefault() return on your server? As in:- TimeZone timeZone = TimeZone.getDefault(); timeZone.getDisplayName(); timeZone.getID(); timeZone.getOffset( System.currentTimeMillis() ); HTH. Rob