You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Ashish Sharma (Jira)" <ji...@apache.org> on 2021/09/04 10:42:00 UTC
[jira] [Comment Edited] (HIVE-25499) select unix_timestamp(dt) from
table and select unix_timestamp(constant date) are different
[ https://issues.apache.org/jira/browse/HIVE-25499?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17409933#comment-17409933 ]
Ashish Sharma edited comment on HIVE-25499 at 9/4/21, 10:41 AM:
----------------------------------------------------------------
query -
create table testdate(dt date);
insert into testdate values('0001-12-30');
select * from testdate;
select unix_timestamp(dt) from testdate;
select unix_timestamp('0001-12-30', 'yyyy-MM-dd');
output -
PREHOOK: query: create table testdate(dt date)
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@testdate
POSTHOOK: query: create table testdate(dt date)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: database:default
POSTHOOK: Output: default@testdate
PREHOOK: query: insert into testdate values('0001-12-30')
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@testdate
POSTHOOK: query: insert into testdate values('0001-12-30')
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@testdate
POSTHOOK: Lineage: testdate.dt SCRIPT []
PREHOOK: query: select * from testdate
PREHOOK: type: QUERY
PREHOOK: Input: default@testdate
POSTHOOK: query: select * from testdate
POSTHOOK: type: QUERY
POSTHOOK: Input: default@testdate
0001-12-30
PREHOOK: query: select unix_timestamp(dt) from testdate
PREHOOK: type: QUERY
PREHOOK: Input: default@testdate
POSTHOOK: query: select unix_timestamp(dt) from testdate
POSTHOOK: type: QUERY
POSTHOOK: Input: default@testdate
-62104205222
PREHOOK: query: select unix_timestamp('0001-12-30', 'yyyy-MM-dd')
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: query: select unix_timestamp('0001-12-30', 'yyyy-MM-dd')
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
-62104205222
was (Author: ashish-kumar-sharma):
query -
create table testdate(dt date);
insert into testdate values('0001-12-30');
select * from testdate;
select unix_timestamp(dt) from testdate;
select unix_timestamp('0001-12-30', 'yyyy-MM-dd');
output -
PREHOOK: query: create table testdate(dt date)
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@testdate
POSTHOOK: query: create table testdate(dt date)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: database:default
POSTHOOK: Output: default@testdate
PREHOOK: query: insert into testdate values('0001-12-30')
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@testdate
POSTHOOK: query: insert into testdate values('0001-12-30')
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@testdate
POSTHOOK: Lineage: testdate.dt SCRIPT []
PREHOOK: query: select * from testdate
PREHOOK: type: QUERY
PREHOOK: Input: default@testdate
#### A masked pattern was here ####
POSTHOOK: query: select * from testdate
POSTHOOK: type: QUERY
POSTHOOK: Input: default@testdate
#### A masked pattern was here ####
0001-12-30
PREHOOK: query: select unix_timestamp(dt) from testdate
PREHOOK: type: QUERY
PREHOOK: Input: default@testdate
#### A masked pattern was here ####
POSTHOOK: query: select unix_timestamp(dt) from testdate
POSTHOOK: type: QUERY
POSTHOOK: Input: default@testdate
#### A masked pattern was here ####
-62104205222
PREHOOK: query: select unix_timestamp('0001-12-30', 'yyyy-MM-dd')
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
#### A masked pattern was here ####
POSTHOOK: query: select unix_timestamp('0001-12-30', 'yyyy-MM-dd')
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
#### A masked pattern was here ####
-62104205222
> select unix_timestamp(dt) from table and select unix_timestamp(constant date) are different
> --------------------------------------------------------------------------------------------
>
> Key: HIVE-25499
> URL: https://issues.apache.org/jira/browse/HIVE-25499
> Project: Hive
> Issue Type: Bug
> Affects Versions: 3.1.2
> Reporter: zhaolong
> Assignee: Ashish Sharma
> Priority: Major
>
> I found select unix_timestamp(date column) from table and select unix_timestamp(constant date) are different in 3.1.2, for example:
> create table testdate(dt date);
> insert into testdate values('0001-12-30');
> select * from testdate; --> 0001-12-30
> select unix_timestamp(dt) from testdate; --> -62104233600
> select unix_timestamp('0001-12-30', 'yyyy-MM-dd'); --> -62104406400
> the -62104233600 is different with -62104406400.
>
> and convert timestap value is:
> select from_unixtime(-62104233600); --> 0002-01-01 00:00:00 , 62104233600 is select unix_timestamp(date column) from table value which date is 0001-12-30.
> select from_unixtime(-62104406400); --> 0001-12-30 00:00:00
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)