Hi,
I work for a company where developers are based in Stockholm (CET) and I am based in london.
We have a time_dimension table in our DW which looks like this for the developers:
Time_dimension_id Full_time hour minute second
1 00:00:00 0 0 0
2 00:00:01 0 0 1
3 00:00:02 0 0 2
However on mine, it shows up as:
Time_dimension_id Full_time hour minute second
1 01:00:00 0 0 0
2 01:00:01 0 0 1
3 01:00:02 0 0 2
As you can see there is an error with my full_time column. This is because I am not in CET time zone like the developers.
I have tried other client software and with theirs im able to change my timezone to ‘CET’ and the problem is fixed. However I cant seem to change the timezone within SquirreL SQL.
Any help would be appreciated!
SQuirreL SQL client time zone problem
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: SQuirreL SQL client time zone problem
Hi!
It's pretty easy to change the timezone for your session. Just edit your database connection Alias and add the ConnSettings parameter.
Example:
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'CET'
This parameter is used to execute SQL after the connection is made. In this case it is setting your session timezone...
Hopefully this helps!
It's pretty easy to change the timezone for your session. Just edit your database connection Alias and add the ConnSettings parameter.
Example:
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'CET'
This parameter is used to execute SQL after the connection is made. In this case it is setting your session timezone...
Hopefully this helps!
Jim Knicely
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: SQuirreL SQL client time zone problem
Thanks for the quick reply.
I changed the URL to :
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'CET'
and also
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'UTC'
I then ran 'check timezone' and both times it changed the timezone successfully.
However when I looked at the time_dimension table I still got:
Time_dimension_id Full_time hour minute second
1 01:00:00 0 0 0
2 01:00:01 0 0 1
3 01:00:02 0 0 2
Have any other tips?
I changed the URL to :
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'CET'
and also
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'UTC'
I then ran 'check timezone' and both times it changed the timezone successfully.
However when I looked at the time_dimension table I still got:
Time_dimension_id Full_time hour minute second
1 01:00:00 0 0 0
2 01:00:01 0 0 1
3 01:00:02 0 0 2
Have any other tips?
Re: SQuirreL SQL client time zone problem
if you go on alias properties, and then select the driver properties... there is a property called 'connsettings', if i tick the specify box then I should be able to hardcode in a value. However i cant seem to type anything in the 'value' box, its just a drop down box with the value '?'
Is it like that for you?
Is it like that for you?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: SQuirreL SQL client time zone problem
Yeah. I couldn't figure out how to set that parameter value on the config screen which is why I just added it to the alias string.
Jim Knicely
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: SQuirreL SQL client time zone problem
I changed the URL to :
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'CET'
and also
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'UTC'
I then ran 'check timezone' and both times it changed the timezone successfully.
However when I looked at the time_dimension table I still got:
Time_dimension_id Full_time hour minute second
1 01:00:00 0 0 0
2 01:00:01 0 0 1
3 01:00:02 0 0 2
Have any other tips?
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'CET'
and also
jdbc:vertica://10.255.XX.XXX:5433/v_database?ConnSettings=SET TIMEZONE TO 'UTC'
I then ran 'check timezone' and both times it changed the timezone successfully.
However when I looked at the time_dimension table I still got:
Time_dimension_id Full_time hour minute second
1 01:00:00 0 0 0
2 01:00:01 0 0 1
3 01:00:02 0 0 2
Have any other tips?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: SQuirreL SQL client time zone problem
What is the data type of the FULL_TIME column? Is it TIME or TIME WITH TIME ZONE?
Jim Knicely
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.