Hive select query with Group by minute

Question

I have data of the form as below:

hive> desc test;
no string
txdate string
hive>

hive> select * from test;

1  2014-06-01 10:12:12.100

1  2014-06-01 10:12:14.100

1  2014-06-01 10:12:16.100

1  2014-06-01 10:13:12.100

1  2014-06-01 10:14:12.100

I need to group the data using txdate column and truncated to the last minute. Output should be as below

3  2014-06-01 10:12:00

1  2014-06-01 10:13:00

1  2014-06-01 10:14:00

Can anyone help me with the Hive select query to solve this?

Answer 1

We can use substr() function to make this possible.

Query is:

select substr(txdate,1,16), sum(no) from test group by substr(txdate,1,16);

The result of this query will be

2014-06-01 10:12 3
2014-06-01 10:13 1
2014-06-01 10:14 1