Thread: 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 >
Attachment
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 >
Attachment
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
Attachment
--- 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