Обсуждение: Set timezone in ODBC

We have just setup our first PostgreSQL system. We have a vendor product that connects via ODBC. That vendor product needs to talk in a different timezone than our database server is at. How can you specify a timezone to use through ODBC?

Jed S. Walker

Interesting question. It makes me wonder, is ODBC supposed to be a smart
pipe or a dumb pipe.

So I ask, it the timestamp being sent, a simple one, with no indication
of timezone.

The postgreSQL has a syntax for timestamps with timezones
hh:mm:ss+/-hh:mm eg (10:30:21-08:00, 09:35:17+05:00). see PostgreSQL
User's guide in the documentation as PDF - just look up datatypes Date
and Times.

Could the client do the translation to UTC/GMT, and that be what is
transmitted.

It seems that the areas DBMSs vary most are DDL (data definition
lanaguage) and DateTime signification.



Walker, Jed S wrote:

> We have just setup our first PostgreSQL system. We have a vendor product
> that connects via ODBC. That vendor product needs to talk in a different
> timezone than our database server is at. How can you specify a timezone
> to use through ODBC?
>
>
>             Jed S. Walker
>

To configure a server for a default timezone, see the PostgreSQL admin
guide for the PostgreSql configuration file, the TIMEZONE parameter.



Walker, Jed S wrote:

> We have just setup our first PostgreSQL system. We have a vendor product
> that connects via ODBC. That vendor product needs to talk in a different
> timezone than our database server is at. How can you specify a timezone
> to use through ODBC?
>
>
>             Jed S. Walker
>

We don't want to change the timezone on the server.

We could have the client send the timestamp with timezone but without
setting the time (using set timezone to) then they get back the
timestamps in the server timezone which they don't want.

-----Original Message-----
From: Greg Campbell [mailto:greg.campbell@us.michelin.com]
Sent: Thursday, June 09, 2005 10:15 AM
To: Walker, Jed S
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Set timezone in ODBC

To configure a server for a default timezone, see the PostgreSQL admin
guide for the PostgreSql configuration file, the TIMEZONE parameter.



Walker, Jed S wrote:

> We have just setup our first PostgreSQL system. We have a vendor
product
> that connects via ODBC. That vendor product needs to talk in a
different
> timezone than our database server is at. How can you specify a
timezone
> to use through ODBC?
>
>
>             Jed S. Walker
>

Curious,
I did a brief experiment on the PostgreSQL server with psql without ODBC
inverviening
.
Something like
SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;
SELECT CURRENT_TIMESTAMP AS TEST_TIME;
SET TIME ZONE DEFAULT;
SELECT CURRENT_TIMESTAMP AS TEST_TIME;

It did return timestamps that were marked as 8 hrs. different.

I am wondering if ODBC is just not passing the adjusted time. I don't
know why it would be biased in one direction (message to server being
adjusted, but data coming back not ???)
Is your field of 'timestamp with timezone' or just timestamp?




Walker, Jed S wrote:

> We don't want to change the timezone on the server.
>
> We could have the client send the timestamp with timezone but without
> setting the time (using set timezone to) then they get back the
> timestamps in the server timezone which they don't want.
>
> -----Original Message-----
> From: Greg Campbell [mailto:greg.campbell@us.michelin.com]
> Sent: Thursday, June 09, 2005 10:15 AM
> To: Walker, Jed S
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Set timezone in ODBC
>
> To configure a server for a default timezone, see the PostgreSQL admin
> guide for the PostgreSql configuration file, the TIMEZONE parameter.
>
>
>
> Walker, Jed S wrote:
>
>
>>We have just setup our first PostgreSQL system. We have a vendor
>
> product
>
>>that connects via ODBC. That vendor product needs to talk in a
>
> different
>
>>timezone than our database server is at. How can you specify a
>
> timezone
>
>>to use through ODBC?
>>
>>
>>            Jed S. Walker
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--- Greg Campbell <greg.campbell@us.michelin.com>
wrote:

> Curious,
> I did a brief experiment on the PostgreSQL server
> with psql without ODBC
> inverviening
> .
> Something like
> SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;
> SELECT CURRENT_TIMESTAMP AS TEST_TIME;
> SET TIME ZONE DEFAULT;
> SELECT CURRENT_TIMESTAMP AS TEST_TIME;
>
> It did return timestamps that were marked as 8 hrs.
> different.
>
> I am wondering if ODBC is just not passing the
> adjusted time. I don't
> know why it would be biased in one direction
> (message to server being
> adjusted, but data coming back not ???)
> Is your field of 'timestamp with timezone' or just
> timestamp?

Maybe this is just too blindingly obvious, but I
haven't seen it mentioned in this thread: why not just
issue a "set timezone" command from your client
session?  Has that been tried, and with failure?

>
>
>
>
> Walker, Jed S wrote:
>
> > We don't want to change the timezone on the
> server.
> >
> > We could have the client send the timestamp with
> timezone but without
> > setting the time (using set timezone to) then they
> get back the
> > timestamps in the server timezone which they don't
> want.
> >
> > -----Original Message-----
> > From: Greg Campbell
> [mailto:greg.campbell@us.michelin.com]
> > Sent: Thursday, June 09, 2005 10:15 AM
> > To: Walker, Jed S
> > Cc: pgsql-odbc@postgresql.org
> > Subject: Re: [ODBC] Set timezone in ODBC
> >
> > To configure a server for a default timezone, see
> the PostgreSQL admin
> > guide for the PostgreSql configuration file, the
> TIMEZONE parameter.
> >
> >
> >
> > Walker, Jed S wrote:
> >
> >
> >>We have just setup our first PostgreSQL system. We
> have a vendor
> >
> > product
> >
> >>that connects via ODBC. That vendor product needs
> to talk in a
> >
> > different
> >
> >>timezone than our database server is at. How can
> you specify a
> >
> > timezone
> >
> >>to use through ODBC?
> >>
> >>
> >>            Jed S. Walker
> >>
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> > begin:vcard
> fn:Greg Campbell
> n:Campbell;Greg
> org:Michelin North America - US5 Lexington;ENG-ASE
> email;internet:greg.campbell@us.michelin.com
> title:ASE Systems Engineer
> tel;work:803-951-5561/x75561
> x-mozilla-html:FALSE
> version:2.1
> end:vcard
>
> >
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>




__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail

Unfortunately I'm not doing the ODBC piece, but I had suggested that.
Maybe they just don't know how to issue "set timezone" through an ODBC
interface? Any thoughts on that?

-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Friday, June 10, 2005 7:56 AM
To: Walker, Jed S
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Set timezone in ODBC

--- Greg Campbell <greg.campbell@us.michelin.com>
wrote:

> Curious,
> I did a brief experiment on the PostgreSQL server with psql without
> ODBC inverviening .
> Something like
> SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE; SELECT
> CURRENT_TIMESTAMP AS TEST_TIME; SET TIME ZONE DEFAULT; SELECT
> CURRENT_TIMESTAMP AS TEST_TIME;
>
> It did return timestamps that were marked as 8 hrs.
> different.
>
> I am wondering if ODBC is just not passing the adjusted time. I don't
> know why it would be biased in one direction (message to server being
> adjusted, but data coming back not ???) Is your field of 'timestamp
> with timezone' or just timestamp?

Maybe this is just too blindingly obvious, but I haven't seen it
mentioned in this thread: why not just issue a "set timezone" command
from your client session?  Has that been tried, and with failure?

>
>
>
>
> Walker, Jed S wrote:
>
> > We don't want to change the timezone on the
> server.
> >
> > We could have the client send the timestamp with
> timezone but without
> > setting the time (using set timezone to) then they
> get back the
> > timestamps in the server timezone which they don't
> want.
> >
> > -----Original Message-----
> > From: Greg Campbell
> [mailto:greg.campbell@us.michelin.com]
> > Sent: Thursday, June 09, 2005 10:15 AM
> > To: Walker, Jed S
> > Cc: pgsql-odbc@postgresql.org
> > Subject: Re: [ODBC] Set timezone in ODBC
> >
> > To configure a server for a default timezone, see
> the PostgreSQL admin
> > guide for the PostgreSql configuration file, the
> TIMEZONE parameter.
> >
> >
> >
> > Walker, Jed S wrote:
> >
> >
> >>We have just setup our first PostgreSQL system. We
> have a vendor
> >
> > product
> >
> >>that connects via ODBC. That vendor product needs
> to talk in a
> >
> > different
> >
> >>timezone than our database server is at. How can
> you specify a
> >
> > timezone
> >
> >>to use through ODBC?
> >>
> >>
> >>            Jed S. Walker
> >>
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org begin:vcard
> fn:Greg Campbell
> n:Campbell;Greg
> org:Michelin North America - US5 Lexington;ENG-ASE
> email;internet:greg.campbell@us.michelin.com
> title:ASE Systems Engineer
> tel;work:803-951-5561/x75561
> x-mozilla-html:FALSE
> version:2.1
> end:vcard
>
> >
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>




__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail

--- "Walker, Jed S" <Jed_Walker@cable.comcast.com>
wrote:

> Unfortunately I'm not doing the ODBC piece, but I
> had suggested that.
> Maybe they just don't know how to issue "set
> timezone" through an ODBC
> interface? Any thoughts on that?

It works the same as any SQL command sent to the
server.  A "set it and forget it" way, if using a DSN
and wanting to use the same timezone option all of the
time, is to include the command in the "connect
settings" box in the DSN window.

>
> -----Original Message-----
> From: Jeff Eckermann
> [mailto:jeff_eckermann@yahoo.com]
> Sent: Friday, June 10, 2005 7:56 AM
> To: Walker, Jed S
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Set timezone in ODBC
>
> --- Greg Campbell <greg.campbell@us.michelin.com>
> wrote:
>
> > Curious,
> > I did a brief experiment on the PostgreSQL server
> with psql without
> > ODBC inverviening .
> > Something like
> > SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;
> SELECT
> > CURRENT_TIMESTAMP AS TEST_TIME; SET TIME ZONE
> DEFAULT; SELECT
> > CURRENT_TIMESTAMP AS TEST_TIME;
> >
> > It did return timestamps that were marked as 8
> hrs.
> > different.
> >
> > I am wondering if ODBC is just not passing the
> adjusted time. I don't
> > know why it would be biased in one direction
> (message to server being
> > adjusted, but data coming back not ???) Is your
> field of 'timestamp
> > with timezone' or just timestamp?
>
> Maybe this is just too blindingly obvious, but I
> haven't seen it
> mentioned in this thread: why not just issue a "set
> timezone" command
> from your client session?  Has that been tried, and
> with failure?
>
> >
> >
> >
> >
> > Walker, Jed S wrote:
> >
> > > We don't want to change the timezone on the
> > server.
> > >
> > > We could have the client send the timestamp with
> > timezone but without
> > > setting the time (using set timezone to) then
> they
> > get back the
> > > timestamps in the server timezone which they
> don't
> > want.
> > >
> > > -----Original Message-----
> > > From: Greg Campbell
> > [mailto:greg.campbell@us.michelin.com]
> > > Sent: Thursday, June 09, 2005 10:15 AM
> > > To: Walker, Jed S
> > > Cc: pgsql-odbc@postgresql.org
> > > Subject: Re: [ODBC] Set timezone in ODBC
> > >
> > > To configure a server for a default timezone,
> see
> > the PostgreSQL admin
> > > guide for the PostgreSql configuration file, the
> > TIMEZONE parameter.
> > >
> > >
> > >
> > > Walker, Jed S wrote:
> > >
> > >
> > >>We have just setup our first PostgreSQL system.
> We
> > have a vendor
> > >
> > > product
> > >
> > >>that connects via ODBC. That vendor product
> needs
> > to talk in a
> > >
> > > different
> > >
> > >>timezone than our database server is at. How can
> > you specify a
> > >
> > > timezone
> > >
> > >>to use through ODBC?
> > >>
> > >>
> > >>            Jed S. Walker
> > >>
> > >
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > >                http://archives.postgresql.org
> begin:vcard
> > fn:Greg Campbell
> > n:Campbell;Greg
> > org:Michelin North America - US5 Lexington;ENG-ASE
>
> > email;internet:greg.campbell@us.michelin.com
> > title:ASE Systems Engineer
> > tel;work:803-951-5561/x75561
> > x-mozilla-html:FALSE
> > version:2.1
> > end:vcard
> >
> > >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> unregister command
> >     (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
>
>
>
>
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your
> mobile phone.
> http://mobile.yahoo.com/learn/mail
>




__________________________________
Discover Yahoo!
Get on-the-go sports scores, stock quotes, news and more. Check it out!
http://discover.yahoo.com/mobile.html

What would they need to put in the "connect settings" box?   "set
timezone to -03" ??

-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Friday, June 10, 2005 12:38 PM
To: Walker, Jed S
Cc: pgsql-odbc@postgresql.org
Subject: RE: [ODBC] Set timezone in ODBC

--- "Walker, Jed S" <Jed_Walker@cable.comcast.com>
wrote:

> Unfortunately I'm not doing the ODBC piece, but I had suggested that.
> Maybe they just don't know how to issue "set timezone" through an ODBC

> interface? Any thoughts on that?

It works the same as any SQL command sent to the server.  A "set it and
forget it" way, if using a DSN and wanting to use the same timezone
option all of the time, is to include the command in the "connect
settings" box in the DSN window.

>
> -----Original Message-----
> From: Jeff Eckermann
> [mailto:jeff_eckermann@yahoo.com]
> Sent: Friday, June 10, 2005 7:56 AM
> To: Walker, Jed S
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Set timezone in ODBC
>
> --- Greg Campbell <greg.campbell@us.michelin.com>
> wrote:
>
> > Curious,
> > I did a brief experiment on the PostgreSQL server
> with psql without
> > ODBC inverviening .
> > Something like
> > SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;
> SELECT
> > CURRENT_TIMESTAMP AS TEST_TIME; SET TIME ZONE
> DEFAULT; SELECT
> > CURRENT_TIMESTAMP AS TEST_TIME;
> >
> > It did return timestamps that were marked as 8
> hrs.
> > different.
> >
> > I am wondering if ODBC is just not passing the
> adjusted time. I don't
> > know why it would be biased in one direction
> (message to server being
> > adjusted, but data coming back not ???) Is your
> field of 'timestamp
> > with timezone' or just timestamp?
>
> Maybe this is just too blindingly obvious, but I haven't seen it
> mentioned in this thread: why not just issue a "set timezone" command
> from your client session?  Has that been tried, and with failure?
>
> >
> >
> >
> >
> > Walker, Jed S wrote:
> >
> > > We don't want to change the timezone on the
> > server.
> > >
> > > We could have the client send the timestamp with
> > timezone but without
> > > setting the time (using set timezone to) then
> they
> > get back the
> > > timestamps in the server timezone which they
> don't
> > want.
> > >
> > > -----Original Message-----
> > > From: Greg Campbell
> > [mailto:greg.campbell@us.michelin.com]
> > > Sent: Thursday, June 09, 2005 10:15 AM
> > > To: Walker, Jed S
> > > Cc: pgsql-odbc@postgresql.org
> > > Subject: Re: [ODBC] Set timezone in ODBC
> > >
> > > To configure a server for a default timezone,
> see
> > the PostgreSQL admin
> > > guide for the PostgreSql configuration file, the
> > TIMEZONE parameter.
> > >
> > >
> > >
> > > Walker, Jed S wrote:
> > >
> > >
> > >>We have just setup our first PostgreSQL system.
> We
> > have a vendor
> > >
> > > product
> > >
> > >>that connects via ODBC. That vendor product
> needs
> > to talk in a
> > >
> > > different
> > >
> > >>timezone than our database server is at. How can
> > you specify a
> > >
> > > timezone
> > >
> > >>to use through ODBC?
> > >>
> > >>
> > >>            Jed S. Walker
> > >>
> > >
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > >                http://archives.postgresql.org
> begin:vcard
> > fn:Greg Campbell
> > n:Campbell;Greg
> > org:Michelin North America - US5 Lexington;ENG-ASE
>
> > email;internet:greg.campbell@us.michelin.com
> > title:ASE Systems Engineer
> > tel;work:803-951-5561/x75561
> > x-mozilla-html:FALSE
> > version:2.1
> > end:vcard
> >
> > >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> unregister command
> >     (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
>
>
>
>
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>




__________________________________
Discover Yahoo!
Get on-the-go sports scores, stock quotes, news and more. Check it out!
http://discover.yahoo.com/mobile.html

--- "Walker, Jed S" <Jed_Walker@cable.comcast.com>
wrote:

> What would they need to put in the "connect
> settings" box?   "set
> timezone to -03" ??

Whatever works on the command line should work there
also.

>
> -----Original Message-----
> From: Jeff Eckermann
> [mailto:jeff_eckermann@yahoo.com]
> Sent: Friday, June 10, 2005 12:38 PM
> To: Walker, Jed S
> Cc: pgsql-odbc@postgresql.org
> Subject: RE: [ODBC] Set timezone in ODBC
>
> --- "Walker, Jed S" <Jed_Walker@cable.comcast.com>
> wrote:
>
> > Unfortunately I'm not doing the ODBC piece, but I
> had suggested that.
> > Maybe they just don't know how to issue "set
> timezone" through an ODBC
>
> > interface? Any thoughts on that?
>
> It works the same as any SQL command sent to the
> server.  A "set it and
> forget it" way, if using a DSN and wanting to use
> the same timezone
> option all of the time, is to include the command in
> the "connect
> settings" box in the DSN window.
>
> >
> > -----Original Message-----
> > From: Jeff Eckermann
> > [mailto:jeff_eckermann@yahoo.com]
> > Sent: Friday, June 10, 2005 7:56 AM
> > To: Walker, Jed S
> > Cc: pgsql-odbc@postgresql.org
> > Subject: Re: [ODBC] Set timezone in ODBC
> >
> > --- Greg Campbell <greg.campbell@us.michelin.com>
> > wrote:
> >
> > > Curious,
> > > I did a brief experiment on the PostgreSQL
> server
> > with psql without
> > > ODBC inverviening .
> > > Something like
> > > SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;
> > SELECT
> > > CURRENT_TIMESTAMP AS TEST_TIME; SET TIME ZONE
> > DEFAULT; SELECT
> > > CURRENT_TIMESTAMP AS TEST_TIME;
> > >
> > > It did return timestamps that were marked as 8
> > hrs.
> > > different.
> > >
> > > I am wondering if ODBC is just not passing the
> > adjusted time. I don't
> > > know why it would be biased in one direction
> > (message to server being
> > > adjusted, but data coming back not ???) Is your
> > field of 'timestamp
> > > with timezone' or just timestamp?
> >
> > Maybe this is just too blindingly obvious, but I
> haven't seen it
> > mentioned in this thread: why not just issue a
> "set timezone" command
> > from your client session?  Has that been tried,
> and with failure?
> >
> > >
> > >
> > >
> > >
> > > Walker, Jed S wrote:
> > >
> > > > We don't want to change the timezone on the
> > > server.
> > > >
> > > > We could have the client send the timestamp
> with
> > > timezone but without
> > > > setting the time (using set timezone to) then
> > they
> > > get back the
> > > > timestamps in the server timezone which they
> > don't
> > > want.
> > > >
> > > > -----Original Message-----
> > > > From: Greg Campbell
> > > [mailto:greg.campbell@us.michelin.com]
> > > > Sent: Thursday, June 09, 2005 10:15 AM
> > > > To: Walker, Jed S
> > > > Cc: pgsql-odbc@postgresql.org
> > > > Subject: Re: [ODBC] Set timezone in ODBC
> > > >
> > > > To configure a server for a default timezone,
> > see
> > > the PostgreSQL admin
> > > > guide for the PostgreSql configuration file,
> the
> > > TIMEZONE parameter.
> > > >
> > > >
> > > >
> > > > Walker, Jed S wrote:
> > > >
> > > >
> > > >>We have just setup our first PostgreSQL
> system.
> > We
> > > have a vendor
> > > >
> > > > product
> > > >
> > > >>that connects via ODBC. That vendor product
> > needs
> > > to talk in a
> > > >
> > > > different
> > > >
> > > >>timezone than our database server is at. How
> can
> > > you specify a
> > > >
> > > > timezone
> > > >
> > > >>to use through ODBC?
> > > >>
> > > >>
> > > >>            Jed S. Walker
> > > >>
> > > >
> > > >
> > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > >                http://archives.postgresql.org
> > begin:vcard
> > > fn:Greg Campbell
> > > n:Campbell;Greg
> > > org:Michelin North America - US5
> Lexington;ENG-ASE
> >
> > > email;internet:greg.campbell@us.michelin.com
> > > title:ASE Systems Engineer
> > > tel;work:803-951-5561/x75561
> > > x-mozilla-html:FALSE
> > > version:2.1
> > > end:vcard
> > >
> > > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with
> the
> > unregister command
> > >     (send "unregister YourEmailAddressHere" to
> > > majordomo@postgresql.org)
> > >
> >
> >
> >
> >
> > __________________________________
> > Yahoo! Mail Mobile
> > Take Yahoo! Mail with you! Check email on your
> mobile phone.
> > http://mobile.yahoo.com/learn/mail
> >
>
>
>
>
> __________________________________
> Discover Yahoo!
> Get on-the-go sports scores, stock quotes, news and
> more. Check it out!
> http://discover.yahoo.com/mobile.html
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>




__________________________________
Discover Yahoo!
Stay in touch with email, IM, photo sharing and more. Check it out!
http://discover.yahoo.com/stayintouch.html