Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here. Want to know more about what has changed? Check out the Community News blog.

from_unixtime format with literal H

SOLVED Go to solution

from_unixtime format with literal H

Explorer

I would like to use from_unixtime to convert a unix_timestamp to a string in the format 2017-04-13H08 where the 08 is the hour of day.  I haven't figured out how I can do that.  I've tried several approaches with backslashes and quotes without success.

 

Here is an example that would produce 2017-04-13T08.

 

[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-13 08:10:11'),'yyyy-MM-ddTHH');
Query: select from_unixtime(unix_timestamp('2017-04-13 08:10:11'),'yyyy-MM-ddTHH')
+-----------------------------------------------------------------------+
| from_unixtime(unix_timestamp('2017-04-13 08:10:11'), 'yyyy-mm-ddthh') |
+-----------------------------------------------------------------------+
| 2017-04-13T08 |
+-----------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s

 

Here are some of my attempts:

[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-ddHHH');
Query: select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-ddHHH')
+-----------------------------------------------------------------------+
| from_unixtime(unix_timestamp('2017-04-08 09:10:11'), 'yyyy-mm-ddhhh') |
+-----------------------------------------------------------------------+
| 2017-04-08009 |
+-----------------------------------------------------------------------+
Fetched 1 row(s) in 0.02s
[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\\HHH');
Query: select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\\HHH')
+-------------------------------------------------------------------------+
| from_unixtime(unix_timestamp('2017-04-08 09:10:11'), 'yyyy-mm-dd\\hhh') |
+-------------------------------------------------------------------------+
| 2017-04-08\009 |
+-------------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s
[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\'H\'HH');
Query: select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\'H\'HH')
+---------------------------------------------------------------------------+
| from_unixtime(unix_timestamp('2017-04-08 09:10:11'), 'yyyy-mm-dd\'h\'hh') |
+---------------------------------------------------------------------------+
| 2017-04-08'9'09 |
+---------------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s
[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\\'H\\'HH');
Query: select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\\'H\\'HH')
ERROR: AnalysisException: Syntax error in line 1:
... 09:10:11'),'yyyy-MM-dd\\'H\\'HH')
^
Encountered: IDENTIFIER
Expected: AND, BETWEEN, DIV, HAVING, ILIKE, IN, IREGEXP, IS, LIKE, LIMIT, NOT, OFFSET, OR, ORDER, RANGE, REGEXP, RLIKE, ROWS, UNION, COMMA

CAUSED BY: Exception: Syntax error

[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\\\'H\\\'HH');
Query: select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd\\\'H\\\'HH')
+-------------------------------------------------------------------------------+
| from_unixtime(unix_timestamp('2017-04-08 09:10:11'), 'yyyy-mm-dd\\\'h\\\'hh') |
+-------------------------------------------------------------------------------+
| 2017-04-08\'9\'09 |
+-------------------------------------------------------------------------------+
Fetched 1 row(s) in 0.02s
[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd''H''HH');
Query: select from_unixtime(unix_timestamp('2017-04-08 09:10:11'),'yyyy-MM-dd''H''HH')
ERROR: AnalysisException: Syntax error in line 1:
...08 09:10:11'),'yyyy-MM-dd''H''HH')
^
Encountered: STRING LITERAL
Expected: AND, BETWEEN, DIV, HAVING, ILIKE, IN, IREGEXP, IS, LIKE, LIMIT, NOT, OFFSET, OR, ORDER, RANGE, REGEXP, RLIKE, ROWS, UNION, COMMA

CAUSED BY: Exception: Syntax error

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: from_unixtime format with literal H

Cloudera Employee
Impala does not support backslashes/quotes to avoid interpretation of
format letters.

To get the desired format, you can do something like this:
select regexp_replace(from_unixtime(unix_timestamp('2017-04-13
08:10:11'),'yyyy-MM-ddTHH'), 'T', 'H');
2 REPLIES 2
Highlighted

Re: from_unixtime format with literal H

Cloudera Employee
Impala does not support backslashes/quotes to avoid interpretation of
format letters.

To get the desired format, you can do something like this:
select regexp_replace(from_unixtime(unix_timestamp('2017-04-13
08:10:11'),'yyyy-MM-ddTHH'), 'T', 'H');

Re: from_unixtime format with literal H

Explorer

Thank you for your quick response.  It's a bit disappointing that impala differs from hive:

hive> select from_unixtime(1492677561,'yyyy-MM-dd\'H\'HH');
OK
_c0
2017-04-20H08

I've created https://issues.apache.org/jira/browse/IMPALA-5237