Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - from_unix time problem


Copy link to this message
-
Re: from_unix time problem
Stephen Sprague 2013-09-17, 16:47
what happens if you use HH (instead of hh)?  Any diff?
On Tue, Sep 17, 2013 at 8:49 AM, Robert Li <[EMAIL PROTECTED]> 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>*
>