[
https://issues.apache.org/jira/browse/HIVE-22852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Yavorski updated HIVE-22852:
-----------------------------------
Description:
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}
The 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.
was:
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.
> 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
> Priority: Major
>
> 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}
>
>
> The 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)
