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
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
0
Comments
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: Then compare your timezone with Timezone in New_time function. there should be a difference of 10 hrs.
Hope this helps.
NC
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: Lets convert CURRENT_TIMESTAMP to UTC: -5 hours
Lets convert a result to EST(America/New_York):
-10 hours
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?
1. Can you share client name?
2. Did you reported a bug to client developers?
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 .
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.