CODEWITHSUNDEEP
sqlhiveqlapache-hive
knowads
knowads

Reputation: 795

HiveQL - Query Number of Entries over fixed unit of time

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

Upvotes: 1

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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;

Upvotes: 1

Related Questions