hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ryan Harris (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-3822) Casting from double to timestamp type seems inconsistent
Date Sat, 05 Jan 2013 20:48:12 GMT

    [ https://issues.apache.org/jira/browse/HIVE-3822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13544959#comment-13544959
] 

Ryan Harris commented on HIVE-3822:
-----------------------------------

I'm still not sure that I agree with you on the time conversion issue...
I'm not sure what the "correct" answer is, but I'm experiencing inconsistent/unexpected behavior
depending on how I convert the timestamp.

>From my point of view, if a timezone isn't specified then 0 = 1970-01-01 00:00:00

select cast(0.0 as timestamp) from test limit 1
1969-12-31 17:00:00

select cast('1970-01-01 00:00:00' as timestamp) from test limit 1
1970-01-01 00:00:00

Shouldn't those two results be the same?
This is the "inconsistency" I reference.

select to_utc_timestamp(0.0,'GMT') from test limit 1
1969-12-31 17:00:00

select to_utc_timestamp('1970-01-01 00:00:00','GMT') from test limit 1
1970-01-01 00:00:00

The same inconsistency is produced here....but the behavior seemed to be related to the cast()
conversion taking place in to_utc_timestamp, not in the UDF itself...if you still think that
this is actually an issue with the UDF itself I can file a separate JIRA for that, but to
me, all of this seems to be related back to cast()

In this case, my cluster is set to America/Denver timezone, so if I attempted to set a local
epoch time to a timestamp in what to me is a logical approach, this *appears* to work:
select to_utc_timestamp(0.0,'America/Denver') from test limit 1
1970-01-01 00:00:00

However, I say that it "appears" to work, because if I the retrieve that same stored timestamp
using the from_utc_timestamp() UDF:
select from_utc_timestamp(to_utc_timestamp(0.0,'America/Denver'),'America/Denver') from test
limit 1
1969-12-31 17:00:00

which again is different than if I had done:
"select from_utc_timestamp(to_utc_timestamp('1970-01-01 00:00:00','America/Denver'),'America/Denver')
from test limit 1
1970-01-01 00:00:00


The problem is that cast(STRING datetime to TIMESTAMP) produces different results from cast(int/float
datetime to TIMESTAMP)

                
> Casting from double to timestamp type seems inconsistent
> --------------------------------------------------------
>
>                 Key: HIVE-3822
>                 URL: https://issues.apache.org/jira/browse/HIVE-3822
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 0.9.0
>            Reporter: Mark Grover
>            Assignee: Mark Grover
>
> {code}
> select cast(1355944339 as timestamp) from decimal_3 limit 1;
> 1970-01-16 08:39:04.339
> select cast(1355944339000 as timestamp) from decimal_3 limit 1;
> 2012-12-19 11:12:19
> select cast(1355944339.123456789 as timestamp) from decimal_3 limit 1;
> 2012-12-19 11:12:19.1234567
> {code}
> If specifying the unixTimestamp without a decimal point, we need to specify the millisecond
timestamp. If specifying with a decimal point, we need to specify only the second timestamp
and the rest goes after decimal. Moreover, it seems like some precision is lost (notice '89'
are lost in the last query.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message