[MDEV-8234] CONNECT fails for TIMESTAMP Created: 2015-05-26  Updated: 2015-06-02  Resolved: 2015-05-30

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.17
Fix Version/s: 10.0.20

Type: Bug Priority: Major
Reporter: Takuya Aoki (Inactive) Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: connect-engine
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.

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.

 Comments   
Comment by Olivier Bertrand [ 2015-05-28 ]

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.

Comment by Olivier Bertrand [ 2015-05-30 ]

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.

Comment by Takuya Aoki (Inactive) [ 2015-06-02 ]

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.

Comment by Olivier Bertrand [ 2015-06-02 ]

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.

Comment by Takuya Aoki (Inactive) [ 2015-06-02 ]

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

Generated at Thu Feb 08 07:25:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.