hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vidya Sujeet <sjayatheer...@gmail.com>
Subject Re: Converting datime formats in Hive - please help
Date Mon, 27 Oct 2014 00:46:26 GMT
oh, great. thanks a lot. works for me as well!!

On Sun, Oct 26, 2014 at 4:47 PM, Lefty Leverenz <leftyleverenz@gmail.com>
wrote:

> The link in the wiki
> <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions>
> to Java documentation for SimpleDateFormat was broken; the new link is:
> http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html.
>
> -- Lefty
>
> On Sun, Oct 26, 2014 at 7:30 PM, Harpreet Bedi <harpreet.bedi9@gmail.com>
> wrote:
>
>> Hi Vidya,
>>
>> I tried recreating a similar hive table and it worked fine. Here is a
>> snippet from my terminal. I hope it helps.
>>
>> I created a table name “testone” with three columns “name” “todaydate”
>> and “lastname”
>> with the row data as:  “Metallica” “-5-FEB-01”  “Today”.
>>
>> TERMINAL SNIPPET
>>
>> hive> *select * from testone;*
>> OK
>> *Metallica 05-FEB-01 Today*
>> Time taken: 1.345 seconds, Fetched: 1 row(s)
>> hive> *select from_unixtime(unix_timestamp(todaydate,'dd-MMM-yy')) from
>> testone;*
>> Query ID = root_20141026181818_b37aef53-4c0f-4b24-a54a-f25491a1807f
>> Total jobs = 1
>> Launching Job 1 out of 1
>> Number of reduce tasks is set to 0 since there's no reduce operator
>> Starting Job = job_1414362909167_0001, Tracking URL =
>> http://sandbox.hortonworks.com:8088/proxy/application_1414362909167_0001/
>> Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill
>> job_1414362909167_0001
>> Hadoop job information for Stage-1: number of mappers: 1; number of
>> reducers: 0
>> 2014-10-26 18:19:57,297 Stage-1 map = 0%,  reduce = 0%
>> 2014-10-26 18:20:57,752 Stage-1 map = 0%,  reduce = 0%
>> 2014-10-26 18:21:19,374 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU
>> 5.98 sec
>> MapReduce Total cumulative CPU time: 5 seconds 980 msec
>> Ended Job = job_1414362909167_0001
>> MapReduce Jobs Launched:
>> Job 0: Map: 1   Cumulative CPU: 5.98 sec   HDFS Read: 247 HDFS Write: 20
>> SUCCESS
>> Total MapReduce CPU Time Spent: 5 seconds 980 msec
>> OK
>> *2001-02-05 00:00:00*
>> Time taken: 182.391 seconds, Fetched: 1 row(s)
>>
>>
>> Harpreet Bedi
>> On Oct 26, 2014, at 5:11 PM, Vidya Sujeet <sjayatheertha@gmail.com>
>> wrote:
>>
>> yes, this retunrs NULLs.. from_unixtime(unix_timestamp('05-FEB-01',
>> 'dd-MMM-yy '))
>>
>>
>> On Sun, Oct 26, 2014 at 2:09 PM, Harpreet Bedi <harpreet.bedi9@gmail.com>
>> wrote:
>>
>>> 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> 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> wrote:
>>>>>
>>>>> Yup, that should work!
>>>>>
>>>>> On Oct 26, 2014, at 15:00, Vidya Sujeet <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> wrote:
>>>>>
>>>>>> (dd-mmm-yy) returns NULL
>>>>>>
>>>>>> On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet <
>>>>>> 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> wrote:
>>>>>>>
>>>>>>>> Timestamp class is there to do this.
>>>>>>>> On Oct 27, 2014 12:26 AM, "Vidya Sujeet" <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