Skip to main content

Timezone showing in GMT even though it's PST

  • July 2, 2013
  • 4 replies
  • 17 views

Mohit_Anchlia
Forum|alt.badge.img+1
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

4 replies

Forum|alt.badge.img+2
  • Participating Frequently
  • October 3, 2013
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
Forum|alt.badge.img
  • Participating Frequently
  • October 4, 2013
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
Forum|alt.badge.img
  • Participating Frequently
  • October 4, 2013
Thanks

Shobhit_Garg
Forum|alt.badge.img
  • Participating Frequently
  • October 4, 2013
Thanks!