Details

    Description

      I am trying to make a CONNECT table to a table on an IBM database.
      I failed to read TIMESTAMP columns.

      Below is the official manual describing netezza TIMESTAMP, it is a replacement for DATETIME in the other database.

      http://www-01.ibm.com/support/knowledgecenter/SSBJG3_2.5.0/com.ibm.gen_busug.doc/c_fgl_odiagntz_005.htm

      Since TIMESTAMP in MariaDB can hold only limited values,

      '1970-01-01 00:00:01' (UTC) to '2038-01-09 03:14:07' (UTC)

      I made a CONNECT table defining the column definition as DATETIME which supports values,

      '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

      The problem I am getting is that when there is the value,

      '9999-12-31 23:59:59'

      on the original table, I get the below from the CONNECT table.

      '1970-01-01 09:00:00'

      The time difference from UTC in Japan is just 9 hours.
      Does this mean there is some conversion done when a TIMESTAMP (original table) value is converted to a DATETIME (CONNECT) value?

      When I created the CONNECT table making the column definition TIMESTAMP, it returned the below

      '0000-00-00 00:00:00'

      This might make sense because the original value exceeds the limit for TIMESTAMP on MariaDB.

      Attachments

        Activity

          Internally CONNECT handles all dates as timestamp. Dates not belonging to the timestamp limits should be handled as character strings.
          The additional time shift when converting dates to timestamp comes from CONNECT always adding or substracting the local time shift so retrieving a date value from a table always returns the same value wherever the server is.
          Note that leaving CONNECT do the column definition is a facility that as some limits. When there are issues, a better solution is to get the default column definitions via a catalog table, then to use this information to manually creating the table with eventual modifications.

          bertrandop Olivier Bertrand added a comment - Internally CONNECT handles all dates as timestamp. Dates not belonging to the timestamp limits should be handled as character strings. The additional time shift when converting dates to timestamp comes from CONNECT always adding or substracting the local time shift so retrieving a date value from a table always returns the same value wherever the server is. Note that leaving CONNECT do the column definition is a facility that as some limits. When there are issues, a better solution is to get the default column definitions via a catalog table, then to use this information to manually creating the table with eventual modifications.

          As a matter of facts, the time shift was wrongly applied to NULL dates. It is possible that fixing that might also apply to what you got with unsupported dates.

          bertrandop Olivier Bertrand added a comment - As a matter of facts, the time shift was wrongly applied to NULL dates. It is possible that fixing that might also apply to what you got with unsupported dates.
          takuya Takuya Aoki (Inactive) added a comment - - edited

          I didn't know about the below limitations of the DATE datatype.
          I guess the original '9999-12-31 23:59:59' is stored as a 0 second.
          Shouldn't it be stored as a NULL date?

          As you say, the time shift should only be applied to proper dates between the limits.

          https://mariadb.com/kb/en/mariadb/connect-data-types/#date-data-type

          Internally, date/time values are stored by CONNECT as a signed 4-byte integer. The value 0 corresponds to 01 January 1970 12:00:00 am coordinated universal time (UTC). All other date/time values are represented by the number of seconds elapsed since or before midnight (00:00:00), 1 January 1970, to that date/time value. Date/time values before midnight 1 January 1970 are represented by a negative number of seconds.
           
          CONNECT handles dates from 13 December 1901, 20:45:52 to 18 January 2038, 19:14:07.

          takuya Takuya Aoki (Inactive) added a comment - - edited I didn't know about the below limitations of the DATE datatype. I guess the original '9999-12-31 23:59:59' is stored as a 0 second. Shouldn't it be stored as a NULL date? As you say, the time shift should only be applied to proper dates between the limits. https://mariadb.com/kb/en/mariadb/connect-data-types/#date-data-type Internally, date/time values are stored by CONNECT as a signed 4-byte integer. The value 0 corresponds to 01 January 1970 12:00:00 am coordinated universal time (UTC). All other date/time values are represented by the number of seconds elapsed since or before midnight (00:00:00), 1 January 1970, to that date/time value. Date/time values before midnight 1 January 1970 are represented by a negative number of seconds.   CONNECT handles dates from 13 December 1901, 20:45:52 to 18 January 2038, 19:14:07.
          bertrandop Olivier Bertrand added a comment - - edited

          That is what the fix I made should do. Improper dates will be internally stored as zero. This value will be regarded as null or just the date 1970-01-01 depending on the delaration of the column (whether it is declared as NOT NULL or as DEFAULT NULL). This is what CONNECT does for all data types.

          bertrandop Olivier Bertrand added a comment - - edited That is what the fix I made should do. Improper dates will be internally stored as zero. This value will be regarded as null or just the date 1970-01-01 depending on the delaration of the column (whether it is declared as NOT NULL or as DEFAULT NULL). This is what CONNECT does for all data types.

          I see, I guess configuring the NULL option will fix the problem for both DATETIME and TIMESTAMP.

          takuya Takuya Aoki (Inactive) added a comment - I see, I guess configuring the NULL option will fix the problem for both DATETIME and TIMESTAMP.

          People

            bertrandop Olivier Bertrand
            takuya Takuya Aoki (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.