Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Impala converting input time to null during daylight savings switch

Highlighted

Impala converting input time to null during daylight savings switch

New Contributor

Came across a strange issue where in when converting a timestamp from PST to GMT, to_utc_timestamp function is returning blank. I could see that this happening for an hour range only on the day daylight savings were switched off in US.

Query: select to_utc_timestamp(cast('2017-11-05 01:00:00' as timestamp),'PST')

Query: select to_utc_timestamp(cast('2017-11-05 01:59:59' as timestamp),'PST')

Above queries return blank output.

However the same things works fine in hive:

Query: select to_utc_timestamp(cast('2017-11-05 01:00:00' as timestamp),'PST')
OK
2017-11-05 09:00:00
Query: select to_utc_timestamp(cast('2017-11-05 01:59:59' as timestamp),'PST')
OK
2017-11-05 09:59:59

Need help understanding reason for the same and also how to work around this issue using Impala query itself.

Impala Version - v2.7.0 on CDH 5.10

Hive version - 1.1.0 on CDH 5.10

5 REPLIES 5

Re: Impala converting input time to null during daylight savings switch

Master Collaborator

Hi Peenuts,

  My understanding is that this is a tricky case that different systems handle differently because it's ambiguous - in PST there are actually two points in time when it was 2017-11-05 1:00:00, so either the system needs to arbitrarily decide which one it was, possibly returning an incorrect result, or it needs to return NULL or some error.

 

Impala made the second decision but it seems like the Java libraries used by Hive made the first decision. I'm checking with a colleague to see if we have it documented anywhere.

 

Re: Impala converting input time to null during daylight savings switch

Cloudera Employee

Hi Peenuts,

 

In Impala we try to have the following policy when converting timestamps to different timezones: In case of ambiguity, return NULL. Ambiguity means that we could return more than one answer. When daylight savings time switch happens in the fall, the same hour happens twice (between 1-2 am), so if we see "1:30 am", we can't tell if that point in time is before the swtich, or after. Interestingly, when the daylight savings switch happens in the spring, the hour between 2am and 3am does not exist at all.

Re: Impala converting input time to null during daylight savings switch

New Contributor

Thanks for the reply.

Is there any plan to handle it like it is being done in Hive?

 

Regards

Pushkin

Re: Impala converting input time to null during daylight savings switch

Master Collaborator

I don't think there's a plan to make a spot-fix for this issue. We're looking at a general revamp of our timezone conversion to be faster and more robust - see https://issues.apache.org/jira/browse/IMPALA-3307 . I think as part of that switch we'll get more flexibility to handle edge cases like this so we could re-evaluate the behaviour then.

 

Re: Impala converting input time to null during daylight savings switch

New Contributor

Thank you.

Don't have an account?
Coming from Hortonworks? Activate your account here