CODEWITHSUNDEEP
hivehiveql
axmug
axmug

Reputation: 466

Get a substring in hive

I am trying to get a substring of a string from Hive. I have a string as this one: 2017-06-05 09:06:32.0

What I want is to get the first two digits from hour, that is, 09. I get the entire hour with this command:

SELECT SUBSTR(hora,11) AS subhoras FROM axmugbcn18.bbdd WHERE hora =  '2017-06-05 09:06:32.0'

The result of the command is: 09:06:32.0

In order to get only 09 I try this command:

SELECT REGEXP_EXTRACT(hora,'\d\d') AS subhoras FROM axmugbcn18.bbdd WHERE hora = '2017-06-05 09:09:32.0'

but results are blank.

How can I retrieve only the two digits of hour?

Thanks

Upvotes: 3

Views: 14466

Answers (2)

notNull
notNull

Reputation: 31470

There are several ways you can extract hours from timestamp value.

1.Using Substring function:

select substring(string("2017-06-05 09:06:32.0"),12,2);
+------+--+
| _c0  |
+------+--+
| 09   |
+------+--+

2.Using Regexp_Extract:

select regexp_Extract(string("2017-06-05 09:06:32.0"),"\\s(\\d\\d)",1);
+------+--+
| _c0  |
+------+--+
| 09   |
+------+--+

3.Using Hour:

select hour(timestamp("2017-06-05 09:06:32.0"));
+------+--+
| _c0  |
+------+--+
| 9    |
+------+--+

4.Using from_unixtime:

select from_unixtime(unix_timestamp('2017-06-05 09:06:32.0'),'HH');
+------+--+
| _c0  |
+------+--+
| 09   |
+------+--+

5.Using date_format:

select date_format(string('2017-06-05 09:06:32.0'),'hh');
+------+--+
| _c0  |
+------+--+
| 09   |
+------+--+

6.Using Split:

select split(split(string('2017-06-05 09:06:32.0'),' ')[1],':')[0];
+------+--+
| _c0  |
+------+--+
| 09   |
+------+--+

Upvotes: 6

Rishu S
Rishu S

Reputation: 3968

Try the below:

select 
'2017-06-05 09:06:32.0' as t, 
hour('2017-06-05 09:06:32.0'),                               -- output: 9
from_unixtime(unix_timestamp('2017-06-05 09:06:32.0'),'HH')  -- output: 09
from table_name;

You can either try hour or unixtimestamp to get the desired result.

Hope this helps :)

Upvotes: 0

Related Questions