from_unixtime format with literal H

loubershad — Fri, 16 Sep 2022 11:27:27 GMT

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

Re: from_unixtime format with literal H

attilaj — Thu, 13 Apr 2017 18:27:45 GMT

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

loubershad — Thu, 20 Apr 2017 20:33:02 GMT

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

question from_unixtime format with literal H in Archives of Support Questions (Read Only)

from_unixtime format with literal H

Re: from_unixtime format with literal H

Re: from_unixtime format with literal H