Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-5668

Subsecond Unix times around the first supported TIMESTAMP may be wrong

    XMLWordPrintableJSON

Details

    Description

      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:

      [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
      

      This is wrong, it should be NULL:

      [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
      

      This also affects the DECIMAL to TIMESTAMP conversion path:

      [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                            |
      +----------------------------------------------------------+
      

      For reference, here's what postgres does (it supports a wider timestamp range):

      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)
      

      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:

        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);
        }
      

      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.

      Attachments

        Issue Links

          Activity

            People

              csringhofer Csaba Ringhofer
              mjacobs Matthew Jacobs
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: