You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Robert Li <ro...@kontagent.com> on 2013/09/17 17:49:12 UTC
from_unix time problem
Hi All
I have a query where I want to convert the unix timestamp to an hour and
day bucket like so.
*select case *
*when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:01" and "00:15"
then "00:15"*
*when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:16" and "00:30"
then "00:30"*
*.*
*when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:46" and "01:00"
then "01:00"*
*.*
*when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "12:46" and "13:00"
then "13:00"
*
*.*
*.*
But the problem is it seems like it's converting both 01:00 and 13:00 into
the same bucket in the result. How can I tell from_unixtime to convert the
times into a 24 format and not 12 hour format?
timebucket date
01:00 2013-09-17 13:00:05
01:00 2013-09-17 01:00:05
--
*
Robert Li | Integration and Support Engineer | Kontagent
Kontagent is Hiring! <http://www.kontagent.com/why/careers/> | Check us out
in the press! <http://www.kontagent.com/why/news/>*
*Check out our blog: kScope <http://kaleidoscope.kontagent.com/> | Twitter:
@Kontagent <http://www.twitter.com/kontagent>*
Re: from_unix time problem
Posted by Robert Li <ro...@kontagent.com>.
Thanks, that worked!!
On Tue, Sep 17, 2013 at 12:56 PM, Mark Grover <ma...@apache.org> wrote:
> Stephen is right. Here is some context:
> Looking at the UDF source code (
> https://github.com/apache/hive/blob/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFFromUnixTime.java#L121),
> it's using SimpleDateFormat where the format string follows the convention
> at
> http://docs.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html
> So, 'HH' or 'kk' is likely what you want, based on whether you want 0 or
> 1-referenced hours.
>
>
> On Tue, Sep 17, 2013 at 9:47 AM, Stephen Sprague <sp...@gmail.com>wrote:
>
>> what happens if you use HH (instead of hh)? Any diff?
>>
>>
>> On Tue, Sep 17, 2013 at 8:49 AM, Robert Li <ro...@kontagent.com>wrote:
>>
>>> Hi All
>>>
>>> I have a query where I want to convert the unix timestamp to an hour and
>>> day bucket like so.
>>>
>>> *select case *
>>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:01" and
>>> "00:15" then "00:15"*
>>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:16" and
>>> "00:30" then "00:30"*
>>> *.*
>>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:46" and
>>> "01:00" then "01:00"*
>>> *.*
>>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "12:46" and
>>> "13:00" then "13:00"
>>> *
>>> *.*
>>> *.*
>>>
>>> But the problem is it seems like it's converting both 01:00 and 13:00
>>> into the same bucket in the result. How can I tell from_unixtime to convert
>>> the times into a 24 format and not 12 hour format?
>>>
>>> timebucket date
>>> 01:00 2013-09-17 13:00:05
>>> 01:00 2013-09-17 01:00:05
>>> --
>>> *
>>>
>>> Robert Li | Integration and Support Engineer | Kontagent
>>> Kontagent is Hiring! <http://www.kontagent.com/why/careers/> | Check us
>>> out in the press! <http://www.kontagent.com/why/news/>*
>>> *Check out our blog: kScope <http://kaleidoscope.kontagent.com/> |
>>> Twitter: @Kontagent <http://www.twitter.com/kontagent>*
>>>
>>
>>
>
--
*
Robert Li | Integration and Support Engineer | Kontagent
Kontagent is Hiring! <http://www.kontagent.com/why/careers/> | Check us out
in the press! <http://www.kontagent.com/why/news/>*
*Check out our blog: kScope <http://kaleidoscope.kontagent.com/> | Twitter:
@Kontagent <http://www.twitter.com/kontagent>*
Re: from_unix time problem
Posted by Mark Grover <ma...@apache.org>.
Stephen is right. Here is some context:
Looking at the UDF source code (
https://github.com/apache/hive/blob/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFFromUnixTime.java#L121),
it's using SimpleDateFormat where the format string follows the convention
at
http://docs.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html
So, 'HH' or 'kk' is likely what you want, based on whether you want 0 or
1-referenced hours.
On Tue, Sep 17, 2013 at 9:47 AM, Stephen Sprague <sp...@gmail.com> wrote:
> what happens if you use HH (instead of hh)? Any diff?
>
>
> On Tue, Sep 17, 2013 at 8:49 AM, Robert Li <ro...@kontagent.com>wrote:
>
>> Hi All
>>
>> I have a query where I want to convert the unix timestamp to an hour and
>> day bucket like so.
>>
>> *select case *
>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:01" and
>> "00:15" then "00:15"*
>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:16" and
>> "00:30" then "00:30"*
>> *.*
>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:46" and
>> "01:00" then "01:00"*
>> *.*
>> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "12:46" and
>> "13:00" then "13:00"
>> *
>> *.*
>> *.*
>>
>> But the problem is it seems like it's converting both 01:00 and 13:00
>> into the same bucket in the result. How can I tell from_unixtime to convert
>> the times into a 24 format and not 12 hour format?
>>
>> timebucket date
>> 01:00 2013-09-17 13:00:05
>> 01:00 2013-09-17 01:00:05
>> --
>> *
>>
>> Robert Li | Integration and Support Engineer | Kontagent
>> Kontagent is Hiring! <http://www.kontagent.com/why/careers/> | Check us
>> out in the press! <http://www.kontagent.com/why/news/>*
>> *Check out our blog: kScope <http://kaleidoscope.kontagent.com/> |
>> Twitter: @Kontagent <http://www.twitter.com/kontagent>*
>>
>
>
Re: from_unix time problem
Posted by Stephen Sprague <sp...@gmail.com>.
what happens if you use HH (instead of hh)? Any diff?
On Tue, Sep 17, 2013 at 8:49 AM, Robert Li <ro...@kontagent.com> wrote:
> Hi All
>
> I have a query where I want to convert the unix timestamp to an hour and
> day bucket like so.
>
> *select case *
> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:01" and
> "00:15" then "00:15"*
> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:16" and
> "00:30" then "00:30"*
> *.*
> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "00:46" and
> "01:00" then "01:00"*
> *.*
> *when from_unixtime(cast(ts as bigint),'hh:mm') BETWEEN "12:46" and
> "13:00" then "13:00"
> *
> *.*
> *.*
>
> But the problem is it seems like it's converting both 01:00 and 13:00 into
> the same bucket in the result. How can I tell from_unixtime to convert the
> times into a 24 format and not 12 hour format?
>
> timebucket date
> 01:00 2013-09-17 13:00:05
> 01:00 2013-09-17 01:00:05
> --
> *
>
> Robert Li | Integration and Support Engineer | Kontagent
> Kontagent is Hiring! <http://www.kontagent.com/why/careers/> | Check us
> out in the press! <http://www.kontagent.com/why/news/>*
> *Check out our blog: kScope <http://kaleidoscope.kontagent.com/> |
> Twitter: @Kontagent <http://www.twitter.com/kontagent>*
>