Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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? [[email protected] ~]$ 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; [[email protected] ~]$ 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) [[email protected] ~]$ date Mon Jul 1 16:16:21 PDT 2013

Comments

  • 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
  • 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();

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.