You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Krishnan Narayanan <kr...@gmail.com> on 2014/06/13 02:01:02 UTC
Fwd: Need help in Date format
Hi All,
I have my date format as 08-Mar-2014 how to I change it to 08-03-2014?
Can I use regexp_replace.
I tried below but not getting the desired output.
regexp_replace(started_dt,"\Jan|\Feb|\Mar|\Apr|\May|\Jun|\Jul|\Aug|\Sep|\Oct|\Nov|\Dec","\01|\02|\03|\04|\05|\06|\07|\08|\09|\10|\11|\12")
Output: 09-1|2|3|4|5|6|7|8|9|10|11|12-2014 16:19:56.
Help is much appreciated.
Thanks
Krishnan
Re: Need help in Date format
Posted by Nishant Kelkar <ni...@gmail.com>.
Hi Krishnan,
Try writing your own UDF for it, it should be simple.
For the internals of the UDF, I'd recommend Joda Time library:
http://www.joda.org/joda-time/
For a good resource on how to write UDFs, here's something:
http://blog.matthewrathbone.com/2013/08/10/guide-to-writing-hive-udfs.html
For more complex GenericUDFs (which you won't need here, probably), here's
something I wrote:
http://blog.spryinc.com/2013/10/writing-hive-genericudfs.html#more
Internal to the UDF, your input format should be 'dd-MON-yyyy' and output
format as 'dd-MM-yyyy'.
Good luck!
Best,
Nishant Kelkar
On Thu, Jun 12, 2014 at 8:33 PM, Andre Araujo <ar...@pythian.com> wrote:
> You can find this is a cheeky trick, but it works as a treat :)
>
> select
> printf('%s-%02.0f-%s',
> substr(started_dt,1,2),
> (2+instr('JanFebMarAprMayJunJulAugSepOctNovDec',
> substr(started_dt,4,3)))/3,
> substr(started_dt,8,4)
> )
>
>
>
> On 13 June 2014 10:01, Krishnan Narayanan <kr...@gmail.com>
> wrote:
>
>> Hi All,
>>
>> I have my date format as 08-Mar-2014 how to I change it to 08-03-2014?
>> Can I use regexp_replace.
>>
>> I tried below but not getting the desired output.
>>
>>
>> regexp_replace(started_dt,"\Jan|\Feb|\Mar|\Apr|\May|\Jun|\Jul|\Aug|\Sep|\Oct|\Nov|\Dec","\01|\02|\03|\04|\05|\06|\07|\08|\09|\10|\11|\12")
>>
>> Output: 09-1|2|3|4|5|6|7|8|9|10|11|12-2014 16:19:56.
>> Help is much appreciated.
>>
>>
>> Thanks
>> Krishnan
>>
>>
>
>
> --
> André Araújo
> Big Data Consultant/Solutions Architect
> The Pythian Group - Australia - www.pythian.com
>
> Office (calls from within Australia): 1300 366 021 x1270
> Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
> Mobile: +61 410 323 559
> Fax: +61 2 9805 0544
> IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
>
> “Success is not about standing at the top, it's the steps you leave behind.”
> — Iker Pou (rock climber)
>
> --
>
>
>
>
Re: Need help in Date format
Posted by Andre Araujo <ar...@pythian.com>.
You can find this is a cheeky trick, but it works as a treat :)
select
printf('%s-%02.0f-%s',
substr(started_dt,1,2),
(2+instr('JanFebMarAprMayJunJulAugSepOctNovDec',
substr(started_dt,4,3)))/3,
substr(started_dt,8,4)
)
On 13 June 2014 10:01, Krishnan Narayanan <kr...@gmail.com> wrote:
> Hi All,
>
> I have my date format as 08-Mar-2014 how to I change it to 08-03-2014?
> Can I use regexp_replace.
>
> I tried below but not getting the desired output.
>
>
> regexp_replace(started_dt,"\Jan|\Feb|\Mar|\Apr|\May|\Jun|\Jul|\Aug|\Sep|\Oct|\Nov|\Dec","\01|\02|\03|\04|\05|\06|\07|\08|\09|\10|\11|\12")
>
> Output: 09-1|2|3|4|5|6|7|8|9|10|11|12-2014 16:19:56.
> Help is much appreciated.
>
>
> Thanks
> Krishnan
>
>
--
André Araújo
Big Data Consultant/Solutions Architect
The Pythian Group - Australia - www.pythian.com
Office (calls from within Australia): 1300 366 021 x1270
Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
Mobile: +61 410 323 559
Fax: +61 2 9805 0544
IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
“Success is not about standing at the top, it's the steps you leave behind.”
— Iker Pou (rock climber)
--
--
Re: Need help in Date format
Posted by Nishant Kelkar <ni...@gmail.com>.
I'd try avoid using the Hive inbuilt from_unixtime and unix_timestamp
functions. They are buggy, in that
they depend on the cluster's timezone. So if some of your cluster nodes
have a different timezone than
others, these functions suffer.
Right now, for what you want, it probably doesn't matter. But I'm just
saying in general.
BTW, Joda Time also has provisions to set/convert timezones :)
On Thu, Jun 12, 2014 at 8:34 PM, Krishnan Narayanan <
krishnan.smile@gmail.com> wrote:
> Thank you very much it worked.
>
>
> On Thu, Jun 12, 2014 at 5:21 PM, Gabriel Eisbruch <
> gabrieleisbruch@gmail.com> wrote:
>
>> Hey,
>> I think you can use from_unixtime(unix_timestamp(your_field, "
>> dd-MMM-yyyy"),"dd-MM-yyyy")
>>
>> Thanks,
>> Gabo.
>>
>>
>> 2014-06-12 21:01 GMT-03:00 Krishnan Narayanan <kr...@gmail.com>:
>>
>> Hi All,
>>>
>>> I have my date format as 08-Mar-2014 how to I change it to 08-03-2014?
>>> Can I use regexp_replace.
>>>
>>> I tried below but not getting the desired output.
>>>
>>>
>>> regexp_replace(started_dt,"\Jan|\Feb|\Mar|\Apr|\May|\Jun|\Jul|\Aug|\Sep|\Oct|\Nov|\Dec","\01|\02|\03|\04|\05|\06|\07|\08|\09|\10|\11|\12")
>>>
>>> Output: 09-1|2|3|4|5|6|7|8|9|10|11|12-2014 16:19:56.
>>> Help is much appreciated.
>>>
>>>
>>> Thanks
>>> Krishnan
>>>
>>>
>>
>
Re: Need help in Date format
Posted by Krishnan Narayanan <kr...@gmail.com>.
Thank you very much it worked.
On Thu, Jun 12, 2014 at 5:21 PM, Gabriel Eisbruch <gabrieleisbruch@gmail.com
> wrote:
> Hey,
> I think you can use from_unixtime(unix_timestamp(your_field, "
> dd-MMM-yyyy"),"dd-MM-yyyy")
>
> Thanks,
> Gabo.
>
>
> 2014-06-12 21:01 GMT-03:00 Krishnan Narayanan <kr...@gmail.com>:
>
> Hi All,
>>
>> I have my date format as 08-Mar-2014 how to I change it to 08-03-2014?
>> Can I use regexp_replace.
>>
>> I tried below but not getting the desired output.
>>
>>
>> regexp_replace(started_dt,"\Jan|\Feb|\Mar|\Apr|\May|\Jun|\Jul|\Aug|\Sep|\Oct|\Nov|\Dec","\01|\02|\03|\04|\05|\06|\07|\08|\09|\10|\11|\12")
>>
>> Output: 09-1|2|3|4|5|6|7|8|9|10|11|12-2014 16:19:56.
>> Help is much appreciated.
>>
>>
>> Thanks
>> Krishnan
>>
>>
>
Re: Need help in Date format
Posted by Gabriel Eisbruch <ga...@gmail.com>.
Hey,
I think you can use from_unixtime(unix_timestamp(your_field, "dd-MMM-yyyy"
),"dd-MM-yyyy")
Thanks,
Gabo.
2014-06-12 21:01 GMT-03:00 Krishnan Narayanan <kr...@gmail.com>:
> Hi All,
>
> I have my date format as 08-Mar-2014 how to I change it to 08-03-2014?
> Can I use regexp_replace.
>
> I tried below but not getting the desired output.
>
>
> regexp_replace(started_dt,"\Jan|\Feb|\Mar|\Apr|\May|\Jun|\Jul|\Aug|\Sep|\Oct|\Nov|\Dec","\01|\02|\03|\04|\05|\06|\07|\08|\09|\10|\11|\12")
>
> Output: 09-1|2|3|4|5|6|7|8|9|10|11|12-2014 16:19:56.
> Help is much appreciated.
>
>
> Thanks
> Krishnan
>
>