NEW_TIME in vertica

Sushmit_Roy

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 

Navin_C

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

id10t

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:

daniel@synapse:~$ echo $TZ
America/New_York

CURRENT_TIMESTAMP = 2014-12-17 18:58:27 EST

Lets convert CURRENT_TIMESTAMP to UTC:

daniel@synapse:~$ 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):

daniel@synapse:~$ date --date='TZ="UTC" 2014-12-17 13:58:27 EST'
Wed Dec 17 08:58:27 EST 201

-10 hours

Sushmit_Roy

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?

id10t

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.

Sushmit_Roy

This was a Client issue I was using . Thanks for the help !!

id10t

Hi!

1. Can you share client name?
2. Did you reported a bug to client developers?

Sushmit_Roy

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 .

Mike_Gong

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.

woot

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.