You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Sergey Yavorski (Jira)" <ji...@apache.org> on 2020/02/07 13:48:00 UTC
[jira] [Updated] (HIVE-22852) Different behavior of the
unix_timestamp function
[ 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)