[jira] [Resolved] (IMPALA-5668) Subsecond Unix times around the first supported TIMESTAMP may be wrong

["Csaba Ringhofer (JIRA)" <ji...@apache.org>]

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

Csaba Ringhofer resolved IMPALA-5668.
-------------------------------------
       Resolution: Fixed
    Fix Version/s: Impala 2.11.0

> Subsecond Unix times around the first supported TIMESTAMP may be wrong
> ----------------------------------------------------------------------
>
>                 Key: IMPALA-5668
>                 URL: https://issues.apache.org/jira/browse/IMPALA-5668
>             Project: IMPALA
>          Issue Type: Bug
>          Components: Backend
>    Affects Versions: Impala 2.8.0
>            Reporter: Matthew Jacobs
>            Assignee: Csaba Ringhofer
>              Labels: correctness, timestamp
>             Fix For: Impala 2.11.0
>
>
> Converting from unix times at the first supported TIMESTAMP second (unix time -17987443201) with partial seconds appear to be wrong.
> E.g. these are handled correctly:
> {code}
> [localhost:21000] > select cast(-17987443201.0 as timestamp);
> +-----------------------------------+
> | cast(-17987443201.0 as timestamp) |
> +-----------------------------------+
> | NULL                              |
> +-----------------------------------+
> Fetched 1 row(s) in 0.01s
> [localhost:21000] > select cast(-17987443200.0 as timestamp);
> +-----------------------------------+
> | cast(-17987443200.0 as timestamp) |
> +-----------------------------------+
> | 1400-01-01 00:00:00               |
> +-----------------------------------+
> Fetched 1 row(s) in 0.01s
> {code}
> This is wrong, it should be NULL:
> {code}
> [localhost:21000] > select cast(-17987443200.1 as timestamp);
> +-----------------------------------+
> | cast(-17987443200.1 as timestamp) |
> +-----------------------------------+
> | 1400-01-01 00:00:00.100000000     |
> +-----------------------------------+
> Fetched 1 row(s) in 0.01s
> {code}
> This also affects the DECIMAL to TIMESTAMP conversion path:
> {code}
> [localhost:21000] > select cast(cast(-17987443200.1 as decimal(31,1)) as timestamp);
> +----------------------------------------------------------+
> | cast(cast(-17987443200.1 as decimal(31,1)) as timestamp) |
> +----------------------------------------------------------+
> | 1400-01-01 00:00:00.100000000                            |
> +----------------------------------------------------------+
> {code}
> For reference, here's what postgres does (it supports a wider timestamp range):
> {code}
> mj=# set timezone to 'GMT';
> SET
> mj=# select to_timestamp(0);
>       to_timestamp      
> ------------------------
>  1970-01-01 00:00:00+00
> (1 row)
> mj=# select to_timestamp(-17987443200.1);
>          to_timestamp          
> -------------------------------
>  1399-12-31 23:59:59.900002+00
> (1 row)
> mj=# select to_timestamp(-17987443200.0);
>       to_timestamp      
> ------------------------
>  1400-01-01 00:00:00+00
> (1 row)
> {code}
> I'm not sure exactly what the issue is, but may be related to boost intervals (e.g. IMPALA-1675 which affected timestamp-functions) in timestamp-value. See:
> {code}
>   static TimestampValue FromUnixTimeNanos(time_t unix_time, int64_t nanos) {
>     boost::posix_time::ptime temp = UnixTimeToPtime(unix_time);
>     temp += boost::posix_time::nanoseconds(nanos);
>     return TimestampValue(temp);
>   }
>   /// Returns a TimestampValue where the integer part of the specified 'unix_time'
>   /// specifies the number of seconds (see above), and the fractional part is converted
>   /// to nanoseconds and added to the resulting TimestampValue.
>   static TimestampValue FromSubsecondUnixTime(double unix_time) {
>     const time_t unix_time_whole = unix_time;
>     boost::posix_time::ptime temp = UnixTimeToPtime(unix_time_whole);
>     temp += boost::posix_time::nanoseconds((unix_time - unix_time_whole) / ONE_BILLIONTH);
>     return TimestampValue(temp);
>   }
> {code}
> Next step is to debug this and figure out exactly why the wrong value is being returned. It shouldn't be too hard because the affected code is very narrow.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)