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 date

Solved Go to solution
Highlighted

Impala date

Explorer

When i am running below

 

select unix_timestamp('20190131 05:00:00',"yyyyMMdd HH:mm:ss");

 

output is 1548910800 

 

but when i am running same thing on a table on a column which is big int

 

select min(IPID.BK_EFF_STRT_DT) ,max(IPID.BK_EFF_STRT_DT) from  ipid;

 

o/p is below

 

+--------------------------+--------------------------+
| min(ipid.bk_eff_strt_dt) | max(ipid.bk_eff_strt_dt) |
+--------------------------+--------------------------+
| 1512709200000 | 1548910800000

 

why is extra 0 added in 2nd case

 

Also

 

select cast(1548910800000 div 1000 as timestamp) + interval (1548910800000 % 1000) milliseconds;

 

returns :

 

2019-01-31 05:00:00

 

This is causing issue when i am trying something like below it returns 0 rows:

 

select count(*) from ipid
where unix_timestamp('20190124',"yyyyMMdd") between IPID.BK_EFF_STRT_DT AND IPID.BK_EFF_END_DT;

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Impala date

Expert Contributor
Hi,
the unix_timestamp function return a number of seconds but it seems that ipid.bk_eff_strt_dt column is inserted with the milliseconds number!?
About your query try this:

SELECT COUNT(*)
FROM ipid
WHERE unix_timestamp('20190124',"yyyyMMdd")*1000 BETWEEN ipid.BK_EFF_STRT_DT AND ipid.BK_EFF_END_DT;

Good luck.
2 REPLIES 2

Re: Impala date

Expert Contributor
Hi,
the unix_timestamp function return a number of seconds but it seems that ipid.bk_eff_strt_dt column is inserted with the milliseconds number!?
About your query try this:

SELECT COUNT(*)
FROM ipid
WHERE unix_timestamp('20190124',"yyyyMMdd")*1000 BETWEEN ipid.BK_EFF_STRT_DT AND ipid.BK_EFF_END_DT;

Good luck.

Re: Impala date

Explorer
Thanks
Will try that,do you have any suggestion on best way to implementing
dimension with scd2 type in hadoop, our dimension table has several sources
and all should be able to load /update concurrently in dimension table.
#- Please type your reply above this line -##
Don't have an account?
Coming from Hortonworks? Activate your account here