Thread: Anyway to tell jdbc to use server timezone?

Anyway to tell jdbc to use server timezone?

From
George Woodring
Date:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net

Re: Anyway to tell jdbc to use server timezone?

From
Dave Cramer
Date:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net

Re: Anyway to tell jdbc to use server timezone?

From
George Woodring
Date:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net


Re: Anyway to tell jdbc to use server timezone?

From
Dave Cramer
Date:
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net



Re: Anyway to tell jdbc to use server timezone?

From
Mikko Tiihonen
Date:

Would an acceptable workaround for you to change the Java default timezone.

Either start the JVM with -Duser.timezone=GMT

or to invoke in your startup code TimeZone.setDefault(TimeZone.get("GMT"));


-Mikko


From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 13 November 2014 16:27
To: George Woodring
Cc: List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net



Re: Anyway to tell jdbc to use server timezone?

From
George Woodring
Date:
I have 100 different customers with different timezones,  I was trying to avoid having to put a lookup table to say if I am connecting to foo set the timezone to bar.  GMT was just the example in the email.

Thanks, 
George

iGLASS Networks
www.iglass.net

On Fri, Nov 14, 2014 at 3:07 AM, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Would an acceptable workaround for you to change the Java default timezone.

Either start the JVM with -Duser.timezone=GMT

or to invoke in your startup code TimeZone.setDefault(TimeZone.get("GMT"));


-Mikko


From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 13 November 2014 16:27
To: George Woodring
Cc: List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net




Re: Anyway to tell jdbc to use server timezone?

From
Dave Cramer
Date:
George,

Looking at the reason it was put in there, this is for the binary protocol. I think you may be able to get away with removing it if you disable binary protocol for timestamps.

Mikko ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 14 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
I have 100 different customers with different timezones,  I was trying to avoid having to put a lookup table to say if I am connecting to foo set the timezone to bar.  GMT was just the example in the email.

Thanks, 
George

iGLASS Networks
www.iglass.net

On Fri, Nov 14, 2014 at 3:07 AM, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Would an acceptable workaround for you to change the Java default timezone.

Either start the JVM with -Duser.timezone=GMT

or to invoke in your startup code TimeZone.setDefault(TimeZone.get("GMT"));


-Mikko


From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 13 November 2014 16:27
To: George Woodring
Cc: List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net





Re: Anyway to tell jdbc to use server timezone?

From
Mikko Tiihonen
Date:

That would be an easy way to get back the old functionality.


Fixing binary protocol to work with server side timezone and adding a way to choose service side timezone would be the correct long term solution.


-Mikko


From: davecramer@gmail.com <davecramer@gmail.com> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 14 November 2014 15:48
To: George Woodring
Cc: Mikko Tiihonen; List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
George,

Looking at the reason it was put in there, this is for the binary protocol. I think you may be able to get away with removing it if you disable binary protocol for timestamps.

Mikko ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 14 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
I have 100 different customers with different timezones,  I was trying to avoid having to put a lookup table to say if I am connecting to foo set the timezone to bar.  GMT was just the example in the email.

Thanks, 
George

iGLASS Networks
www.iglass.net

On Fri, Nov 14, 2014 at 3:07 AM, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Would an acceptable workaround for you to change the Java default timezone.

Either start the JVM with -Duser.timezone=GMT

or to invoke in your startup code TimeZone.setDefault(TimeZone.get("GMT"));


-Mikko


From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 13 November 2014 16:27
To: George Woodring
Cc: List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net





Re: Anyway to tell jdbc to use server timezone?

From
Dave Cramer
Date:
Mikko,
What is involved in the latter ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 14 November 2014 10:04, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

That would be an easy way to get back the old functionality.


Fixing binary protocol to work with server side timezone and adding a way to choose service side timezone would be the correct long term solution.


-Mikko


From: davecramer@gmail.com <davecramer@gmail.com> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 14 November 2014 15:48
To: George Woodring
Cc: Mikko Tiihonen; List

Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
George,

Looking at the reason it was put in there, this is for the binary protocol. I think you may be able to get away with removing it if you disable binary protocol for timestamps.

Mikko ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 14 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
I have 100 different customers with different timezones,  I was trying to avoid having to put a lookup table to say if I am connecting to foo set the timezone to bar.  GMT was just the example in the email.

Thanks, 
George

iGLASS Networks
www.iglass.net

On Fri, Nov 14, 2014 at 3:07 AM, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Would an acceptable workaround for you to change the Java default timezone.

Either start the JVM with -Duser.timezone=GMT

or to invoke in your startup code TimeZone.setDefault(TimeZone.get("GMT"));


-Mikko


From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 13 November 2014 16:27
To: George Woodring
Cc: List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net






Re: Anyway to tell jdbc to use server timezone?

From
George Woodring
Date:
Or is there a way to call a "set timezone = default" to get back to the server timezone.  It would not be hard to do a call like that when we create the connection.

Thanks
George

iGLASS Networks
www.iglass.net

On Fri, Nov 14, 2014 at 12:41 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Mikko,
What is involved in the latter ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 14 November 2014 10:04, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

That would be an easy way to get back the old functionality.


Fixing binary protocol to work with server side timezone and adding a way to choose service side timezone would be the correct long term solution.


-Mikko


From: davecramer@gmail.com <davecramer@gmail.com> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 14 November 2014 15:48
To: George Woodring
Cc: Mikko Tiihonen; List

Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
George,

Looking at the reason it was put in there, this is for the binary protocol. I think you may be able to get away with removing it if you disable binary protocol for timestamps.

Mikko ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 14 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
I have 100 different customers with different timezones,  I was trying to avoid having to put a lookup table to say if I am connecting to foo set the timezone to bar.  GMT was just the example in the email.

Thanks, 
George

iGLASS Networks
www.iglass.net

On Fri, Nov 14, 2014 at 3:07 AM, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Would an acceptable workaround for you to change the Java default timezone.

Either start the JVM with -Duser.timezone=GMT

or to invoke in your startup code TimeZone.setDefault(TimeZone.get("GMT"));


-Mikko


From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 13 November 2014 16:27
To: George Woodring
Cc: List
Subject: Re: [JDBC] Anyway to tell jdbc to use server timezone?
 
Ah, OK, this was done for binary protocol. I seem to recall this is somewhat defined in the spec according to setTimestamp.

Is there a reason why you can't just override this in your code when you get a connection ?



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 13 November 2014 08:31, George Woodring <george.woodring@iglass.net> wrote:
It is not the where case that is my issue.  It is that the jdbc driver is changing my timezone from what I set with alter database to the timezone of my web server for any queries from the web server.  What I would like is a way to keep the database timezone setting.

When I log into my database with psql and show timezone I get "GMT", when I put a log into my java code and show timezone I get "US/Eastern".  This is causing my data not to line up correctly around DST boundries, since the data was save at 12, 2, 4am GMT and it is trying to find data at 12, 2, 4am Eastern which is 12, 1, 3 am GMT

Thanks
George

iGLASS Networks
www.iglass.net

On Wed, Nov 12, 2014 at 1:59 PM, Dave Cramer <pg@fastcrypt.com> wrote:
George,

I looked at the stackoverflow example. I fail to see how the driver would change the where clause.

Can you create a self contained test case ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 November 2014 13:48, George Woodring <george.woodring@iglass.net> wrote:
I originally asked a question about adjusting timezone per db back


And it was suggested that I set them per db, which I have.  But now moving to 
9.3 of the jdbc driver, it seems to set the timezone to the web server's timezone.

I have found mentions


and


My question here is is there a way to disable it and get the older functionality?  Is there a command I could run after I get the connection to say "set timezone = 'server default'"?

Thanks
George Woodring
iGLASS Networks
www.iglass.net