SQuirreL SQL client time zone problem

Moderator: NorbertKrupa

circles85
Newbie
Newbie
Posts: 5
Joined: Tue Jul 03, 2012 12:00 pm

SQuirreL SQL client time zone problem

Post by circles85 » Tue Jul 03, 2012 12:01 pm

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!

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

Re: SQuirreL SQL client time zone problem

Post by JimKnicely » Tue Jul 03, 2012 1:57 pm

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!
Jim Knicely

Image

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

circles85
Newbie
Newbie
Posts: 5
Joined: Tue Jul 03, 2012 12:00 pm

Re: SQuirreL SQL client time zone problem

Post by circles85 » Tue Jul 03, 2012 8:48 pm

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?

circles85
Newbie
Newbie
Posts: 5
Joined: Tue Jul 03, 2012 12:00 pm

Re: SQuirreL SQL client time zone problem

Post by circles85 » Tue Jul 03, 2012 8:54 pm

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?

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

Re: SQuirreL SQL client time zone problem

Post by JimKnicely » Tue Jul 03, 2012 9:32 pm

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

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

circles85
Newbie
Newbie
Posts: 5
Joined: Tue Jul 03, 2012 12:00 pm

Re: SQuirreL SQL client time zone problem

Post by circles85 » Sat Jul 07, 2012 2:04 pm

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?

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

Re: SQuirreL SQL client time zone problem

Post by JimKnicely » Mon Jul 09, 2012 6:42 pm

What is the data type of the FULL_TIME column? Is it TIME or TIME WITH TIME ZONE?
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 “SQuirreL SQL Client”