PDA

View Full Version : How do I add leading zeros to a numeric data field in Hive QL?



rommy
07-09-2012, 06:59 PM
I have a Select statement wherein I'm trying to subtract one date from another resulting in an output of days. The different date components are stored in different columns, so I want to concatenate them, then subtract one from the other. However due to the nature of months and days (single digit or double digit, this is proving to be very difficult (eg. May 12th - May 3rd looks like 512 - 53). I tried to use the "RIGHT" function from SQL, but that doesn't appear to be working in Hive QL.

ex.
select
reg.uid
,CONCAT(reg.month,reg.day) regist_date
,CONCAT(log.month,log.day) login_date
,RIGHT('00000'+ CONVERT(VARCHAR,CONCAT(reg.month,reg.day),6)) as NUM

Help is greatly appreciated. Thanks!

jdixon
07-09-2012, 07:06 PM
Its very hard to subtract dates this way. July 1st will be 601 and June 30th will be 530. When you substract them you will get 70 days difference instead of the correct result of 1.

Take a look at the Hive date functions. You need to use a function like datediff: https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions

James