How do I generate an array of hours from a pair of timestamps in Hive?

Question

I have a table that looks like:

ID   start_time     end_time     username
1    1451674800     1451692800   blah

And I want a table that looks like:

ID   username   hours
1    blah       [11, 12, 13, 14, 15, 16]

Where [11, 12, 13, 14, 15, 16] is an array with values corresponding to the hours (PST) which lie between 1451674800 and 1451692800 (UNIX time).

My thought is that I need to use Hive's timestamp functions, but I am so incredibly unfamiliar with Hive and especially with the timestamp functions that I don't even know where to begin.

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

First create function which convert UNIX time to ordinal time. Can take from here

Then calculate DATEDIFF(hh,start_time,end_time). Add this number of hours begining from DATEPART(dd,start_time) and concatenate them.

edited May 23 '17 at 11:52

Community

1
1

answered Jun 17 '16 at 23:50

tungula

578
1
6
12

DATEDIFF is exactly what I need, thanks! Doesn't [FROM_UNIXTIME](http://www.folkstalk.com/2011/11/date-functions-in-hive.html) accomplish UNIX -> Ordinal conversion? – Danny David Leybzon Jun 20 '16 at 20:17
1

Ah, DATEDIFF in hive only works on differences between dates, not times. – Danny David Leybzon Jun 20 '16 at 20:29

How do I generate an array of hours from a pair of timestamps in Hive?

1 Answers1