Timezone showing in GMT even though it's PST

Mohit_Anchlia

For some reason TIMESTAMP WITH TIMEZONE is returining time in GMT even though default timezone is in pacific. Why would that be? [vertica@host ~]$ cat t show timezone; SELECT CASE WHEN LENGTH(TRIM('1372705611836')) > 10 then TIMESTAMP WITH TIMEZONE 'epoch' + cast ('1372705611836' AS BIGINT)/1000 * INTERVAL '1 second' ELSE null end from dual; SELECT CASE WHEN LENGTH(TRIM('1372705611836')) > 10 then TIMESTAMP 'epoch' + cast ('1372705611836' AS BIGINT)/1000 * INTERVAL '1 second' ELSE null end from dual; [vertica@host ~]$ vsql -d db -h host1 -U vertica -f t Password: name | setting ----------+------------ timezone | US/Pacific (1 row) case ---------------------------- 2013-07-01 12:06:51.836-07 (1 row) case ------------------------- 2013-07-01 19:06:51.836 (1 row) [vertica@host ~]$ date Mon Jul 1 16:16:21 PDT 2013

[Deleted User]

Hi Mohit,

It seems correct to me

I have changed the timezone in vsql at my local environment.

gl=> set timezone US/Pacific ;
SET
gl=> SELECT CASE WHEN LENGTH(TRIM('1372705611836')) > 10 then TIMESTAMP WITH TIMEZONE 'epoch' + cast ('1372705611836' AS BIGINT)/1000 * INTERVAL '1 second' ELSE null end from dual;
            case
----------------------------
 2013-07-01 12:06:51.836-07
(1 row)
Since US/Pacific timezone is -7 hrs from GMT

gl=> SELECT CASE WHEN LENGTH(TRIM('1372705611836')) > 10 then TIMESTAMP 'epoch' + cast ('1372705611836' AS BIGINT)/1000 * INTERVAL '1 second' ELSE null end from dual;
          case
-------------------------
 2013-07-01 19:06:51.836
(1 row)

gl=> show timezone;
   name   |  setting
----------+------------
 timezone | US/Pacific
(1 row)

At your end also we can see the same output
Also to check the current time in database, you can use below
select now();

Thanks

Rahul_Choudhary

Hi Mohit,

It seems correct to me

I have changed the timezone in vsql at my local environment.

gl=> set timezone US/Pacific ;
SET
gl=> SELECT CASE WHEN LENGTH(TRIM('1372705611836')) > 10 then TIMESTAMP WITH TIMEZONE 'epoch' + cast ('1372705611836' AS BIGINT)/1000 * INTERVAL '1 second' ELSE null end from dual;
            case
----------------------------
 2013-07-01 12:06:51.836-07
(1 row)
Since US/Pacific timezone is -7 hrs from GMT

gl=> SELECT CASE WHEN LENGTH(TRIM('1372705611836')) > 10 then TIMESTAMP 'epoch' + cast ('1372705611836' AS BIGINT)/1000 * INTERVAL '1 second' ELSE null end from dual;
          case
-------------------------
 2013-07-01 19:06:51.836
(1 row)

gl=> show timezone;
   name   |  setting
----------+------------
 timezone | US/Pacific
(1 row)

At your end also we can see the same output
Also to check the current time in database, you can use below
select now();

Shobhit_Garg

Thanks

Shobhit_Garg

Thanks!