Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.17
-
Fix Version/s: 10.0.20
-
Component/s: Storage Engine - Connect
-
Labels:
-
Environment: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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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.
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.
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.