[jira] [Updated] (HIVE-21101) storing and loading timestamp changes value

["Markus Steindl (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-21101?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Markus Steindl updated HIVE-21101:
----------------------------------
    Description: 
If we store 1500000000.0 (seconds since epoch) as timestamp,
load it, and cast it back to double, the value is changed.

This happens when writing to the CSV file.
Hive assumes that 1500000000.0 is local timestamp, and converts it to UTC.
However, when reading it, it does not reverse this conversion.
This explains the 7200 seconds offset in the example below.

This behavior is very confusing since a cast from double to timestamp and back to double does not yield the original result.

{{create table timestamp_test(ts_double double, ts timestamp);}}

{{insert into timestamp_test select * from (select 1.5e9, cast(1.5e9 as timestamp)) t;}}

{{select ts_double, cast(ts as double) from timestamp_test;}}
{{-- 1.5E9 1.5000072E9}}



  was:
If we store 1500000000.0 (seconds since epoch) as timestamp,
load it, and cast it back to double, the value is changed.

This happens when writing to the CSV file.
Hive assumes that 1500000000.0 is local timestamp, and converts it to UTC.
However, when reading it, it does not reverse this conversion.
This explains the 7200 seconds offset. 

This behavior is very confusing since a cast from double to timestamp and back to double does not yield the original result.

See the following example:

{{create table timestamp_test(ts_double double, ts timestamp);}}

{{insert into timestamp_test select * from (select 1.5e9, cast(1.5e9 as timestamp)) t;}}

{{select ts_double, cast(ts as double) from timestamp_test;}}
{{-- 1.5E9 1.5000072E9}}




> storing and loading timestamp changes value
> -------------------------------------------
>
>                 Key: HIVE-21101
>                 URL: https://issues.apache.org/jira/browse/HIVE-21101
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 1.2.0
>            Reporter: Markus Steindl
>            Priority: Major
>
> If we store 1500000000.0 (seconds since epoch) as timestamp,
> load it, and cast it back to double, the value is changed.
> This happens when writing to the CSV file.
> Hive assumes that 1500000000.0 is local timestamp, and converts it to UTC.
> However, when reading it, it does not reverse this conversion.
> This explains the 7200 seconds offset in the example below.
> This behavior is very confusing since a cast from double to timestamp and back to double does not yield the original result.
> {{create table timestamp_test(ts_double double, ts timestamp);}}
> {{insert into timestamp_test select * from (select 1.5e9, cast(1.5e9 as timestamp)) t;}}
> {{select ts_double, cast(ts as double) from timestamp_test;}}
> {{-- 1.5E9 1.5000072E9}}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)