hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Harpreet Bedi <harpreet.be...@gmail.com>
Subject Re: Converting datime formats in Hive - please help
Date Sun, 26 Oct 2014 21:09:47 GMT
Hi Vidya,

Have you tried using this instead?

select from_unixtime(unix_timestamp('05-FEB-01', 'dd-MMM-yy '))



On Sunday, October 26, 2014, Vidya Sujeet <sjayatheertha@gmail.com> wrote:

> Hi Harpreet,
>
> I used the below statement but it doesn't give me the correct time. It
> gives me 2000-12-31 instead of 2001/02/05..what am I doing wrong?
>
>  select from_unixtime(unix_timestamp('05-FEB-01', 'DD-MMM-YY'))
> thanks,
> Vidya
>
>
> On Sun, Oct 26, 2014 at 1:06 PM, Harpreet Singh Bedi <
> harpreet.bedi9@gmail.com
> <javascript:_e(%7B%7D,'cvml','harpreet.bedi9@gmail.com');>> wrote:
>
>> You’ll have to use lowercase “y” and lowercase “d” for year and date of
>> the month.
>>
>> On Oct 26, 2014, at 15:03, Harpreet Singh Bedi <Harpreet.Bedi9@gmail.com
>> <javascript:_e(%7B%7D,'cvml','Harpreet.Bedi9@gmail.com');>> wrote:
>>
>> Yup, that should work!
>>
>> On Oct 26, 2014, at 15:00, Vidya Sujeet <sjayatheertha@gmail.com
>> <javascript:_e(%7B%7D,'cvml','sjayatheertha@gmail.com');>> wrote:
>>
>> Ok, the pattern should be (DD-MMM-YY) all in upper case! thanks
>>
>> On Sun, Oct 26, 2014 at 12:47 PM, Vidya Sujeet <sjayatheertha@gmail.com
>> <javascript:_e(%7B%7D,'cvml','sjayatheertha@gmail.com');>> wrote:
>>
>>> (dd-mmm-yy) returns NULL
>>>
>>> On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet <sjayatheertha@gmail.com
>>> <javascript:_e(%7B%7D,'cvml','sjayatheertha@gmail.com');>> wrote:
>>>
>>>> But what should the format be if the source has data coming *"25-FEB-01'
?
>>>> appreciate your help.*
>>>>
>>>> On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu <
>>>> nagarjuna.viss@gmail.com
>>>> <javascript:_e(%7B%7D,'cvml','nagarjuna.viss@gmail.com');>> wrote:
>>>>
>>>>> Timestamp class is there to do this.
>>>>> On Oct 27, 2014 12:26 AM, "Vidya Sujeet" <sjayatheertha@gmail.com
>>>>> <javascript:_e(%7B%7D,'cvml','sjayatheertha@gmail.com');>> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> The date time format coming from the source is* "25-FEB-01' .* I
>>>>>> want to convert it to the following format. *'MM/DD/YYYY' *. How
can
>>>>>> we do this in Hive?
>>>>>>
>>>>>>
>>>>>> I see that as per the documentation
>>>>>>
>>>>>>
>>>>>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
>>>>>>
>>>>>> I could possibly convert the string date to a Unix time stamp in
>>>>>> seconds using the below UDF. However, what is the string pattern
if the
>>>>>> date at source is coming this way* "25-FEB-01'* ? The link provided
>>>>>> to look up for the patterns does not work.
>>>>>>
>>>>>> Please help.
>>>>>>
>>>>>> *Name:* unix_timestamp (string date, string pattern) function.
>>>>>> *Description: *Convert time string with given pattern (see [
>>>>>> http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
>>>>>> to Unix time stamp (in seconds), return 0 if fail:
>>>>>> unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Vidya
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>
>>
>>
>>
>

-- 
Harpreet Bedi

Mime
View raw message