HiveQL - Query Number of Entries over fixed unit of time

Question

I have a table that is similar to the following:

LOGIN ID (STRING):      TIME_STAMP (STRING HH:MM:SS)
BillyJoel               10:45:00
PianoMan                10:45:30
WeDidnt                 10:45:45
StartTheFire            10:46:00
AlwaysBurning           10:46:30

Is there any possible way to get a query that gives me a column of the number of logins over a period of time? Something like this:

3 (number of logins from 10:45:00 - 10:45:59)
2 (number of logins from 10:46:00 - 10:46:59)

Note: If you can only do it with int timestamps, that's alright. My original table is all strings, so I thought I would represent that here. The stuff in parentheses don't need to be printed

score 1 · Accepted Answer · answered Jun 24 '16 at 19:02

1

If you want it by minute, you can just lop off the seconds:

select substr(1, 5, time_stamp) as hhmm, count(*)
from t
group by hhmm
order by hhmm;

answered Jun 24 '16 at 19:02

Gordon Linoff

1,242,037
58
646
786

Thanks, but what if I want a non-orthodox measurement, like 2 minutes or half a minute? – knowads Jun 24 '16 at 19:46
@knowads . . . Then ask another question. That is not quite as easily solved. – Gordon Linoff Jun 25 '16 at 07:18

HiveQL - Query Number of Entries over fixed unit of time

1 Answers1