Get a substring in hive

Question

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

Answer 1

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   |
+------+--+