Sergey Yavorski created HIVE-22852:
--------------------------------------
Summary: Different behavior of the unix_timestamp function
Key: HIVE-22852
URL: https://issues.apache.org/jira/browse/HIVE-22852
Project: Hive
Issue Type: Bug
Reporter: Sergey Yavorski
While working with timestamps before 01.01.1970 unix_timestamp function returns
different results depending on the source of the argument: from query or from
DB.
Assume we have a hive external table TEST_TABLE with fields:
't' of the timestamp type
'id' of the int type, PK.
Assume we store there timestamps before 01.01.1970 00:00:00.000, for example we
have only one record with the value of t = '1958-08-19 19:03:48.62' and with PK
field id = 1.
Then:
{code:java}
select t,
from_unixtime(unix_timestamp(t, 'yyyy-MM-dd HH:mm:ss')) as FROM_DB,
from_unixtime(unix_timestamp('1958-08-19 19:03:48.62', 'yyyy-MM-dd
HH:mm:ss')) AS FROM_SQL
from TEST_TABLE
where id = 1;
+-------------------------+----------------------+----------------------+--+
| t | from_db | from_sql |
+-------------------------+----------------------+----------------------+--+
| 1958-08-19 19:03:48.62 | 1958-08-19 19:03:49 | 1958-08-19 19:03:48 |
+-------------------------+----------------------+----------------------+--+
{code}
Te problem is hidden in the unix_timestamp function:
{code:java}
select unix_timestamp(t, 'yyyy-MM-dd HH:mm:ss') as FROM_DB,
unix_timestamp('1958-08-19 19:03:48.62', 'yyyy-MM-dd HH:mm:ss') AS
FROM_SQL
from TEST_TABLE
where id = 1;
+-------------+-------------+--+
| from_db | from_sql |
+-------------+-------------+--+
| -358761371 | -358761372 |
+-------------+-------------+--+
{code}
For dates after 01.01.1970 everything works fine.
The required solution should change the behavior of the unix_timestamp function
for timestamps before 01.01.1970: the function should return the value smaller
by 1 than the current version returns.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
