SQuirreL SQL client time zone problem

Moderator: NorbertKrupa

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

Re: SQuirreL SQL client time zone problem

Post by circles85 » Thu Jul 12, 2012 10:25 am

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

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

Re: SQuirreL SQL client time zone problem

Post by JimKnicely » Thu Jul 12, 2012 5:08 pm

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'"...
Attachments
SQ_Properties.png
Alias Properties
SQ_Properties.png (47.97 KiB) Viewed 4414 times
Jim Knicely

Image

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

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

Re: SQuirreL SQL client time zone problem

Post by JimKnicely » Thu Jul 12, 2012 6:56 pm

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".
Attachments
TZ_Examples.png
TZ_Example
TZ_Examples.png (4.09 KiB) Viewed 4411 times
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”