Set Default Time Zone for ODBC

Moderator: NorbertKrupa

Post Reply
JeffSatler
Newbie
Newbie
Posts: 21
Joined: Mon Feb 13, 2012 9:44 pm

Set Default Time Zone for ODBC

Post by JeffSatler » Fri Feb 24, 2012 4:20 am

How do I set the default time zone for every new ODBC connection when the timezone we want in the session is different than the default database timezone?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Set Default Time Zone for ODBC

Post by JimKnicely » Tue Feb 28, 2012 4:39 pm

Hi Jeff,

You can set the timezone for a session opened by an ODBC connection via the ConnSettings parameter of the odbc.ini file. This parameter allows us to list SQL commands that will execute when the connection is made. Here we can add the SET TIMEZONE command to change the timezone!

I'll run through a simple example using a Linux ODBC connection with isql. This concept can be applied to any ODBC connection.

First I'll show that the default timezone for my databases is "US/Eastern":

Code: Select all

[root~]# isql vertica_prod01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show timezone;
+-----------+------------+
| name      | setting    |
+-----------+------------+
| timezone  | US/Eastern |
+-----------+------------+
SQLRowCount returns 1
1 rows fetched
My wife is from Harbin, China so I'd like to change the timezone of my session to 'Asia/Harbin'. To do this I have to edit the odbc.ini file and add/edit the "ConnSettings" parameter. After editing my odbc.ini file the DSN looks like this:

Code: Select all

[vertica_prod01]
Driver = Vertica01
Servername = 10.255.100.45
Database = vertica_test
Port = 5433
UserName = hamster
Password = 
ConnSettings=SET TIMEZONE TO 'Asia/Harbin'
Having made the change, I can now connect with isql and see that the timezone has been modified:

Code: Select all

[root~]# isql vertica_prod01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show timezone;
+-----------+-------------+
| name      | setting     |
+-----------+-------------+
| timezone  | Asia/Harbin |
+-----------+-------------+
SQLRowCount returns 1
1 rows fetched
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “ODBC”