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 <sprag...@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 <robert...@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>* >> > >
