SQuirreL SQL client time zone problem

[circles85]

The 'TYPE_NAME' is 'Time'. The 'DATA_TYPE' = '92'.

I ran a few queries:

select full_time at timezone 'CET' test from kdwh3.time_dimension where time_dimension_id = 1
output:
test
02:00:00


select full_time at timezone 'UTC' test from kdwh3.time_dimension where time_dimension_id = 1
output:
test
22:00:00


select * from kdwh3.time_dimension where full_time at timezone 'CET' = '01:00:00'
output:
time_dimension_id full_time hour minute second
3601 02:00:00 1 0 0


select * from kdwh3.time_dimension where full_time at timezone 'UTC = '01:00:00'
output:
time_dimension_id full_time hour minute second
blank

[JimKnicely]

FYI, you can add your own parameters to a Database Alias in SQuirrel. Just right click on the "Driver properties" tab of the "Properties for Alias" window and select "Add". Then you can create another "ConnSetting" property with the value you want, "SET TIMEZONE TO 'CET'"...

[JimKnicely]

circles85,

You wrote:

The 'TYPE_NAME' is 'Time'. The 'DATA_TYPE' = '92'.

Are you sure that TYPE_NAME isn't "TimeTz" for your FULL_TIME column?

The only way I can mimic your scenario on my side is if it is a TIME WITH TIME STAMP.

For example:

I created a table named TIME_TEST:

Code: Select all

dbadmin=> create table time_test (time_no_tz time, time_tz time with time zone);
CREATE TABLE

Then I inserted two times, one in the UTC time zone and one in the CET time zone:

Code: Select all

dbadmin=> create table time_test (tz_name varchar(3), time_no_tz time, time_tz time with time zone);
CREATE TABLE
dbadmin=> insert into time_test values ('UTC', '00:00:00', '00:00:00 UTC');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into time_test values ('CET', '00:00:00', '00:00:00 CET');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from time_test;
 tz_name | time_no_tz |   time_tz
---------+------------+-------------
 UTC     | 00:00:00   | 00:00:00+00
 CET     | 00:00:00   | 00:00:00+01
(2 rows)

Then from SQuirrel I see this image in the attachment "TZ_Example".