Thread: SQL solution for my JDBC timezone issue

SQL solution for my JDBC timezone issue

From
George Woodring
Date:
Anyone have a suggestion for setting the timezone back to the Postgres db default on a connection.  JDBC now sets the timezone to be the client which is my web server and ignores the default timezone that I have set in the DB.  There are large parts of my code that I have never worried about timezones because the DB would handle it.  Before I head down that path, I thought I would check and see if there was an easier way for me to put it back into the database.

My latest issue is I create a date object and set it to 3am PST,  If I save it into a timestamp with timezone the db saves it at 3am PST when I pull it out.  If I save it to timestamp without timezone, I get 6am now where as before I would get 3am.

Any suggestions would be appreciated
George Woodring
iGLASS Networks
www.iglass.net

Re: SQL solution for my JDBC timezone issue

From
Dave Cramer
Date:
Well you could always just put it back to whatever you want when you open the connection  ie "set timezone ...."

 

Dave Cramer

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

On 23 February 2015 at 08:40, George Woodring <george.woodring@iglass.net> wrote:
Anyone have a suggestion for setting the timezone back to the Postgres db default on a connection.  JDBC now sets the timezone to be the client which is my web server and ignores the default timezone that I have set in the DB.  There are large parts of my code that I have never worried about timezones because the DB would handle it.  Before I head down that path, I thought I would check and see if there was an easier way for me to put it back into the database.

My latest issue is I create a date object and set it to 3am PST,  If I save it into a timestamp with timezone the db saves it at 3am PST when I pull it out.  If I save it to timestamp without timezone, I get 6am now where as before I would get 3am.

Any suggestions would be appreciated
George Woodring
iGLASS Networks
www.iglass.net

Re: SQL solution for my JDBC timezone issue

From
George Woodring
Date:
Yes, that is where we think we are heading, the issue is that the code does not know what it needs to be set back to.  We have 90 databases with 5 different time zones.  I was just hoping for a more elegant solution than writing a lookup table that says if you are connecting to db x then set to timezone y.

George Woodring 

iGLASS Networks
www.iglass.net

On Mon, Feb 23, 2015 at 9:16 AM, Dave Cramer <pg@fastcrypt.com> wrote:
Well you could always just put it back to whatever you want when you open the connection  ie "set timezone ...."

 

Dave Cramer

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

On 23 February 2015 at 08:40, George Woodring <george.woodring@iglass.net> wrote:
Anyone have a suggestion for setting the timezone back to the Postgres db default on a connection.  JDBC now sets the timezone to be the client which is my web server and ignores the default timezone that I have set in the DB.  There are large parts of my code that I have never worried about timezones because the DB would handle it.  Before I head down that path, I thought I would check and see if there was an easier way for me to put it back into the database.

My latest issue is I create a date object and set it to 3am PST,  If I save it into a timestamp with timezone the db saves it at 3am PST when I pull it out.  If I save it to timestamp without timezone, I get 6am now where as before I would get 3am.

Any suggestions would be appreciated
George Woodring
iGLASS Networks
www.iglass.net


Re: SQL solution for my JDBC timezone issue

From
Dave Cramer
Date:
Timestamps have always been a bit of a pain since JDBC does not support both with and without timezones. It really only supports timestamps with timezone.

We have made decisions in the driver which are not optimal for everyone. The reason it does this is for binary transfers of data. We need to control which timezone both the server and the client are operating in.

I'm open to suggestions ?

Dave Cramer

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

On 23 February 2015 at 10:42, George Woodring <george.woodring@iglass.net> wrote:
Yes, that is where we think we are heading, the issue is that the code does not know what it needs to be set back to.  We have 90 databases with 5 different time zones.  I was just hoping for a more elegant solution than writing a lookup table that says if you are connecting to db x then set to timezone y.

George Woodring 

iGLASS Networks
www.iglass.net

On Mon, Feb 23, 2015 at 9:16 AM, Dave Cramer <pg@fastcrypt.com> wrote:
Well you could always just put it back to whatever you want when you open the connection  ie "set timezone ...."

 

Dave Cramer

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

On 23 February 2015 at 08:40, George Woodring <george.woodring@iglass.net> wrote:
Anyone have a suggestion for setting the timezone back to the Postgres db default on a connection.  JDBC now sets the timezone to be the client which is my web server and ignores the default timezone that I have set in the DB.  There are large parts of my code that I have never worried about timezones because the DB would handle it.  Before I head down that path, I thought I would check and see if there was an easier way for me to put it back into the database.

My latest issue is I create a date object and set it to 3am PST,  If I save it into a timestamp with timezone the db saves it at 3am PST when I pull it out.  If I save it to timestamp without timezone, I get 6am now where as before I would get 3am.

Any suggestions would be appreciated
George Woodring
iGLASS Networks
www.iglass.net



Re: SQL solution for my JDBC timezone issue

From
Tom Lane
Date:
George Woodring <george.woodring@iglass.net> writes:
> Yes, that is where we think we are heading, the issue is that the code does
> not know what it needs to be set back to.  We have 90 databases with 5
> different time zones.  I was just hoping for a more elegant solution than
> writing a lookup table that says if you are connecting to db x then set to
> timezone y.

"RESET timezone" ?

            regards, tom lane


Re: SQL solution for my JDBC timezone issue

From
Dave Cramer
Date:
For posterity please be aware this will very likely break any timestamps transfer using JDBC and binary transfer. 

This is not recommended for general consumption

Dave Cramer

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

On 23 February 2015 at 10:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
George Woodring <george.woodring@iglass.net> writes:
> Yes, that is where we think we are heading, the issue is that the code does
> not know what it needs to be set back to.  We have 90 databases with 5
> different time zones.  I was just hoping for a more elegant solution than
> writing a lookup table that says if you are connecting to db x then set to
> timezone y.

"RESET timezone" ?

                        regards, tom lane

Re: SQL solution for my JDBC timezone issue

From
George Woodring
Date:
This is what I was looking for, however the JDBC does something to make its timezone the default.

My cluster is set to GMT, I have a DB that is set to US/Pacific,  when I get the connection from JDBC it is US/Eastern.  The reset command does not affect it.  I can set timezone in the code to 'US/Pacific" and I see it change, when I do another RESET timezone it goes back to US/Eastern.

Thanks,
George Woodring

iGLASS Networks
www.iglass.net

On Mon, Feb 23, 2015 at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
George Woodring <george.woodring@iglass.net> writes:
> Yes, that is where we think we are heading, the issue is that the code does
> not know what it needs to be set back to.  We have 90 databases with 5
> different time zones.  I was just hoping for a more elegant solution than
> writing a lookup table that says if you are connecting to db x then set to
> timezone y.

"RESET timezone" ?

                        regards, tom lane

Re: SQL solution for my JDBC timezone issue

From
Adrian Klaver
Date:
On 02/23/2015 12:15 PM, George Woodring wrote:
> This is what I was looking for, however the JDBC does something to make
> its timezone the default.
>
> My cluster is set to GMT, I have a DB that is set to US/Pacific,  when I
> get the connection from JDBC it is US/Eastern.  The reset command does
> not affect it.  I can set timezone in the code to 'US/Pacific" and I see
> it change, when I do another RESET timezone it goes back to US/Eastern.

In your original post you mentioned that access to the databases is
through a Web server.

Is there just one Web server with one time zone?

>
> Thanks,
> George Woodring
>
> iGLASS Networks
> www.iglass.net <http://www.iglass.net>
>
> On Mon, Feb 23, 2015 at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     George Woodring <george.woodring@iglass.net
>     <mailto:george.woodring@iglass.net>> writes:
>     > Yes, that is where we think we are heading, the issue is that the code does
>     > not know what it needs to be set back to.  We have 90 databases with 5
>     > different time zones.  I was just hoping for a more elegant solution than
>     > writing a lookup table that says if you are connecting to db x then set to
>     > timezone y.
>
>     "RESET timezone" ?
>
>                              regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: SQL solution for my JDBC timezone issue

From
Dave Cramer
Date:
George,

Everytime you get a connection the driver will issue set timezone ...

It does not change the default time zone for the server (AFAICS)

Dave Cramer

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

On 23 February 2015 at 15:29, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/23/2015 12:15 PM, George Woodring wrote:
This is what I was looking for, however the JDBC does something to make
its timezone the default.

My cluster is set to GMT, I have a DB that is set to US/Pacific,  when I
get the connection from JDBC it is US/Eastern.  The reset command does
not affect it.  I can set timezone in the code to 'US/Pacific" and I see
it change, when I do another RESET timezone it goes back to US/Eastern.

In your original post you mentioned that access to the databases is through a Web server.

Is there just one Web server with one time zone?


Thanks,
George Woodring

iGLASS Networks
www.iglass.net <http://www.iglass.net>

On Mon, Feb 23, 2015 at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

    George Woodring <george.woodring@iglass.net
    <mailto:george.woodring@iglass.net>> writes:
    > Yes, that is where we think we are heading, the issue is that the code does
    > not know what it needs to be set back to.  We have 90 databases with 5
    > different time zones.  I was just hoping for a more elegant solution than
    > writing a lookup table that says if you are connecting to db x then set to
    > timezone y.

    "RESET timezone" ?

                             regards, tom lane




--
Adrian Klaver
adrian.klaver@aklaver.com

Re: SQL solution for my JDBC timezone issue

From
Tom Lane
Date:
Dave Cramer <pg@fastcrypt.com> writes:
> Everytime you get a connection the driver will issue set timezone ...
> It does not change the default time zone for the server (AFAICS)

Hmm ... depending on exactly how you issue it, it might become the default
for the session, I think.  I seem to recall that parameter settings
included in the startup packet become session-lifespan defaults.  If you
issued the SET as a separate command then a RESET ought to undo it.

            regards, tom lane


Re: SQL solution for my JDBC timezone issue

From
Dave Cramer
Date:


On 23 February 2015 at 16:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dave Cramer <pg@fastcrypt.com> writes:
> Everytime you get a connection the driver will issue set timezone ...
> It does not change the default time zone for the server (AFAICS)

Hmm ... depending on exactly how you issue it, it might become the default
for the session, I think.  I seem to recall that parameter settings
included in the startup packet become session-lifespan defaults.  If you
issued the SET as a separate command then a RESET ought to undo it.

That would explain it then as they are issued in the startup command

Dave Cramer

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

Re: SQL solution for my JDBC timezone issue

From
George Woodring
Date:
 -- In your original post you mentioned that access to the databases is through a Web server.

 -- Is there just one Web server with one time zone?

We have 2 web servers that are clustered together.  They are both set to Eastern since that is the timezone they are located in.

iGLASS Networks
www.iglass.net


Re: SQL solution for my JDBC timezone issue

From
Dave Cramer
Date:
George,


One solution for you might be to write a C function which gets the OS timezone and then you can execute set timezone=server_timezone();



Dave Cramer

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

On 24 February 2015 at 09:25, George Woodring <george.woodring@iglass.net> wrote:
 -- In your original post you mentioned that access to the databases is through a Web server.

 -- Is there just one Web server with one time zone?

We have 2 web servers that are clustered together.  They are both set to Eastern since that is the timezone they are located in.

iGLASS Networks
www.iglass.net



Re: SQL solution for my JDBC timezone issue

From
Adrian Klaver
Date:
On 02/24/2015 06:25 AM, George Woodring wrote:
>   -- In your original post you mentioned that access to the databases is
> through a Web server.
>
>   -- Is there just one Web server with one time zone?
>
> We have 2 web servers that are clustered together.  They are both set to
> Eastern since that is the timezone they are located in.
>


So:

     JDBC Web servers(US/East)  <---> 90 database (5 different timezones)

Therefore everything to the end user is passed through the Web servers?

Is there a reason why the databases have different timezones?

Seems to me less complicated to have all the databases share the UTC
timezone. Then you only have one offset, US/East <--> UTC.

> iGLASS Networks
> www.iglass.net <http://www.iglass.net>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: SQL solution for my JDBC timezone issue

From
George Woodring
Date:


So:

    JDBC Web servers(US/East)  <---> 90 database (5 different timezones)

Therefore everything to the end user is passed through the Web servers?

Is there a reason why the databases have different timezones?

Seems to me less complicated to have all the databases share the UTC timezone. Then you only have one offset, US/East <--> UTC.


We originally did it for simplicity sake, the database is set for our customer's timezone.  So when we grabbed a date from the db, it would in the correct timezone for the viewer, without the web code having to know the timezone.

George Woodring
iGLASS Networks
www.iglass.net

Re: SQL solution for my JDBC timezone issue

From
Gavin Flower
Date:
On 25/02/15 04:29, Adrian Klaver wrote:
> On 02/24/2015 06:25 AM, George Woodring wrote:
>>   -- In your original post you mentioned that access to the databases is
>> through a Web server.
>>
>>   -- Is there just one Web server with one time zone?
>>
>> We have 2 web servers that are clustered together.  They are both set to
>> Eastern since that is the timezone they are located in.
>>
>
>
> So:
>
>     JDBC Web servers(US/East)  <---> 90 database (5 different timezones)
>
> Therefore everything to the end user is passed through the Web servers?
>
> Is there a reason why the databases have different timezones?
>
> Seems to me less complicated to have all the databases share the UTC
> timezone. Then you only have one offset, US/East <--> UTC.
>
>> iGLASS Networks
>> www.iglass.net <http://www.iglass.net>
>>
>>
>
>
Yes I think it sanest to have servers use GMT*, as then it is simple
enough to convert results to whatever local time zone the client is in.

I live in New Zealand, and we are offset from GMT about 12 hours (depend
on seasonal adjustments).  Have no problem getting query results using
my local time zone automatically. - regardless of whether I query from
the database box or another, using psql!


Cheers,
Gavin


*UTC or whatever it is currently fashionable to call it!  :-)


Re: SQL solution for my JDBC timezone issue

From
Adrian Klaver
Date:
On 02/24/2015 11:36 AM, Gavin Flower wrote:
> On 25/02/15 04:29, Adrian Klaver wrote:
>> On 02/24/2015 06:25 AM, George Woodring wrote:
>>>   -- In your original post you mentioned that access to the databases is
>>> through a Web server.
>>>
>>>   -- Is there just one Web server with one time zone?
>>>
>>> We have 2 web servers that are clustered together.  They are both set to
>>> Eastern since that is the timezone they are located in.
>>>
>>
>>
>> So:
>>
>>     JDBC Web servers(US/East)  <---> 90 database (5 different timezones)
>>
>> Therefore everything to the end user is passed through the Web servers?
>>
>> Is there a reason why the databases have different timezones?
>>
>> Seems to me less complicated to have all the databases share the UTC
>> timezone. Then you only have one offset, US/East <--> UTC.
>>
>>> iGLASS Networks
>>> www.iglass.net <http://www.iglass.net>
>>>
>>>
>>
>>
> Yes I think it sanest to have servers use GMT*, as then it is simple
> enough to convert results to whatever local time zone the client is in.
>
> I live in New Zealand, and we are offset from GMT about 12 hours (depend
> on seasonal adjustments).  Have no problem getting query results using
> my local time zone automatically. - regardless of whether I query from
> the database box or another, using psql!
>
>
> Cheers,
> Gavin
>
>
> *UTC or whatever it is currently fashionable to call it!  :-)

Depends on which side of the Channel you want to make happy:)


--
Adrian Klaver
adrian.klaver@aklaver.com