SQuirreL SQL client time zone problem

[circles85]

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!

[JimKnicely]

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!

[circles85]

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]

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?

[JimKnicely]

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.

[circles85]

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?

[JimKnicely]

What is the data type of the FULL_TIME column? Is it TIME or TIME WITH TIME ZONE?