Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.17
-
CentOS release 6.5 (X86_64), PureData Release 7.0 (P-3) , NetezzaSQL ODBCDriver
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.
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.
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.