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

NEW_TIME in vertica

Not sure why vertica NEW_TIME is giving this output?


 select   CURRENT_TIMESTAMP;

--> 2014-12-17 18:58:27 

        select NEW_TIME(CURRENT_TIMESTAMP,'America/New_York','UTC');

-->2014-12-18 04:59:22

Why is this adding 10 hours 




Comments

  • Hi Sushmit,

    The function is working as expected , it will only show the time in which you want to convert the timestamp to.

    What time zone are you in ?
    Try this from vsql:
    show timezone  
    Then compare your timezone with Timezone in New_time function. there should be a difference of 10 hrs.

    Hope this helps.
    NC
  • Hi!

    Function CURRENT_TIMESTAMP returns a value of type TIMESTAMP WITH TIME ZONE, so at first timestamp is converted from your local time to UTC (-5 hours), after it UTC timestamp converted to EST (more -5 hours) in total -10hours.

    Lets emulate it with Bash:

    My local time zone:
    [email protected]:~$ echo $TZ
    America/New_York
    CURRENT_TIMESTAMP = 2014-12-17 18:58:27 EST
    Lets convert CURRENT_TIMESTAMP to UTC:
    [email protected]:~$ date --date='TZ="UTC" 2014-12-17 18:58:27 EST'
    Wed Dec 17 13:58:27 EST 2014
    -5 hours

    Lets convert a result to EST(America/New_York):
    [email protected]:~$ date --date='TZ="UTC" 2014-12-17 13:58:27 EST'
    Wed Dec 17 08:58:27 EST 201
    -10 hours

  • Hi All,
    Thanks for the explanation . So there is something not I am getting--

    1) When I do -- show timezone it gives me UTC

    2) But when I do select CURRENT_TIMESTAMP it gives me 2014-12-18 11:12:28 which is the current time in EST . When I ran this this was time in New York and my understanding is that it should have returned me the current time in UTC which is +5 hours of what I see. 

    Any idea?


  • Hi!
    When I do -- show timezone it gives me UTC
    System and db can in different timezones.
    But when I do select CURRENT_TIMESTAMP it gives me 2014-12-18 11:12:28 which is the current time in EST .
    If so - its a bug. Vertica should return time in UTC. What Vertica version?
    When I ran this this was time in New York
    Does its still EST time and db is in UTC?
    my understanding is that it should have returned me the current time in UTC
    Correct.
  • This was a Client issue I was using . Thanks for the help !!
  • Hi!

    1. Can you share client name?
    2. Did you reported a bug to client developers?
  • 1.DBVisualizer 
    2. Yes did report to them. There is a setting in client which automatically sets the timezone to local timezone , if you are not aware of this its hard to know . You can change the settings though. Hope this helps .

  • It looks like a bug.

    select NEW_TIME('2015-01-18 20:00:00','GMT','EST');      NEW_TIME       
    ---------------------
     2015-01-18 15:00:00
    (1 row)

    select NEW_TIME('2015-01-18 20:00:00','GMT','EDT');
          NEW_TIME       
    ---------------------
     2015-01-18 16:00:00
    (1 row)

    For this time, EST and EDT should be same.
  • I don't believe EST and EDT work that way.  We observe EDT during daylight savings, but EST during non-daylight savings time dates. I think EDT is always -4 and EST is always -5, which you use depends on whether or not daylight savings is actually being observed.  The TZ actually changes. 

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.