You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by sonia gehlot <so...@gmail.com> on 2012/06/26 02:05:37 UTC
Dates in Hive
Hi All,
A simple question on dates, how can I convert integer column dateint in
"yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
Thanks in advance.
Sonia
Re: Dates in Hive
Posted by Matt Tucker <ma...@gmail.com>.
I don't have the language manual in frot of me, but I would suggest casting dateint to string, then using the unix_timestamp function. From_unixtime should get it back into a date time string
Matt
On Jun 25, 2012, at 8:05 PM, sonia gehlot <so...@gmail.com> wrote:
> Hi All,
>
> A simple question on dates, how can I convert integer column dateint in "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
>
> Thanks in advance.
>
> Sonia
Re: Dates in Hive
Posted by sonia gehlot <so...@gmail.com>.
Thanks everyone,
This worked for me: from_unixtime(unix_timestamp(dt,'yyyyMMdd' ),
'yyyy-MM-dd').
-Sonia
On Mon, Jun 25, 2012 at 11:18 PM, VanHuy Pham <hu...@gmail.com> wrote:
> More specific, you need to use three functions in a row:
> 1) Use unix_timestamp(string date, string pattern) to convert the date
> variable, say dt, which you get from a hive query
> 2) Then use from_unixtime(bigint unixtime) to convert the bigint returned
> in 1)
> 3) The use to_date(string timestamp) to convert date-and-time string
> returned in 2) to just date.
>
> Long story short: Let's say dt is the date returned from your hive query,
> then this would do the job:
> to_date(from_unixtime(unix_timestamp(dt,'yyyyMMdd' ) ) )
>
> Keep in mind that MM is capital, in distinguishing from little mm, which
> is used for minutes, so be careful!!
> Also, modify the format yyyyMMdd so that it matches the format of the
> timestamp returned by your hive query.
>
>
> On Mon, Jun 25, 2012 at 10:35 PM, alo alt <wg...@googlemail.com>wrote:
>
>>
>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions
>>
>> --
>> Alexander Alten-Lorenz
>> http://mapredit.blogspot.com
>> German Hadoop LinkedIn Group: http://goo.gl/N8pCF
>>
>> On Jun 26, 2012, at 2:05 AM, sonia gehlot wrote:
>>
>> > Hi All,
>> >
>> > A simple question on dates, how can I convert integer column dateint in
>> > "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
>> >
>> > Thanks in advance.
>> >
>> > Sonia
>>
>>
>
Re: Dates in Hive
Posted by VanHuy Pham <hu...@gmail.com>.
More specific, you need to use three functions in a row:
1) Use unix_timestamp(string date, string pattern) to convert the date
variable, say dt, which you get from a hive query
2) Then use from_unixtime(bigint unixtime) to convert the bigint returned
in 1)
3) The use to_date(string timestamp) to convert date-and-time string
returned in 2) to just date.
Long story short: Let's say dt is the date returned from your hive query,
then this would do the job:
to_date(from_unixtime(unix_timestamp(dt,'yyyyMMdd' ) ) )
Keep in mind that MM is capital, in distinguishing from little mm, which is
used for minutes, so be careful!!
Also, modify the format yyyyMMdd so that it matches the format of the
timestamp returned by your hive query.
On Mon, Jun 25, 2012 at 10:35 PM, alo alt <wg...@googlemail.com> wrote:
>
> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions
>
> --
> Alexander Alten-Lorenz
> http://mapredit.blogspot.com
> German Hadoop LinkedIn Group: http://goo.gl/N8pCF
>
> On Jun 26, 2012, at 2:05 AM, sonia gehlot wrote:
>
> > Hi All,
> >
> > A simple question on dates, how can I convert integer column dateint in
> > "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
> >
> > Thanks in advance.
> >
> > Sonia
>
>
Re: Dates in Hive
Posted by alo alt <wg...@googlemail.com>.
https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctions
--
Alexander Alten-Lorenz
http://mapredit.blogspot.com
German Hadoop LinkedIn Group: http://goo.gl/N8pCF
On Jun 26, 2012, at 2:05 AM, sonia gehlot wrote:
> Hi All,
>
> A simple question on dates, how can I convert integer column dateint in
> "yyyymmdd" format to string column in "yyyy-mm-dd" format in hive.
>
> Thanks in advance.
>
> Sonia