Options
Timezone showing in GMT even though it's PST
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
0
Comments
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
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();