How to export dates and data to database?

Hi I'm trying to export a table I've created in Matlab to an ODBC database. The table is set up with four columns (trade_date, ticker, curve_date, price). These columns are (datetime, char, datetime, double) datatypes, when i execute the command to write to my database (as i have before using only a table with all data being of type Double), i get the following:

Commands:

conn = database.ODBCConnection('DBName','','');
colnames = {'trade_dt','ticker','curve_dt','price'};
datainsert(conn, 'matlab.tblFwds', colnames, t);

summary(t) gives me:

summary(t)
Variables:
    trade_dt: 110×1 datetime
        Values:
            min       19-Oct-2016
            median    19-Oct-2016
            max       19-Oct-2016
    ticker: 110×21 char
    curve_dt: 110×1 datetime
        Values:
            min       01-Nov-2016
            median    16-May-2021
            max       01-Dec-2025
    price: 110×1 double
        Values:
            min         19.5 
            median    23.675 
            max        48.25

and the error i get when trying to insert into the database is:

Error using database.internal.utilities.DatabaseUtils.validateStruct (line 78)
Input structure, dataset or table incorrectly formatted. See help for details
Error in database.odbc.connection/insert (line 114)
        data = database.internal.utilities.DatabaseUtils.validateStruct(data);
Error in database.odbc.connection/datainsert (line 40)
insert( connect,tableName,fieldNames,data );
Error in MorningstarHistoricalCurves (line 68)
                    datainsert(conn, 'matlab.tblGasFwds',colnames, t);

I would appreciate any help on how to insert this table/data into my database, any examples using dates would be greatly appreciated! Thanks in advance!

Matt

[Rikin Mehta]

Hi Matt,

My name is Rikin Mehta. I am the developer working on the Database Toolbox with MathWorks.

Currently, Database Toolbox doesn't support datetime object with insert functionalities (i.e. INSERT/ FASTINSERT or DATAINSERT) for both JDBC and ODBC interface.

In order to insert columns containing datetime objects, you will need to manuallly type-cast the datetime object into date string using 'datestr(<your_datetime_object>,31)', before passing it to one of the insert functionalities.

Here is the documentation link with more details: http://www.mathworks.com/help/database/ug/datainsert.html

Apologies for this inconvenience. We will take a note of your inquiry and consider support for datetime objects in Database Toolbox for a future MATLAB release.

Rikin

[Walter Roberson]

Rikin Mehta, I recommend you make that comment into an Answer

[Luu Pham]

Rikin,

It appears that these insert functions cannot handle any datatype other than double or cells. Is that correct? I'm working with a somewhat large data set and cast some of my variables as ints to conserve memory but I run into errors then I attempt to insert new records into my database.

Thanks in advance for the clarification.

Luu Pham

Answer 1

[Rikin Mehta]

Hi Matt,

My name is Rikin Mehta. I am the developer working on the Database Toolbox with MathWorks.

Currently, Database Toolbox doesn't support datetime object with insert functionalities (i.e. INSERT/ FASTINSERT or DATAINSERT) for both JDBC and ODBC interface.

In order to insert columns containing datetime objects, you will need to manuallly type-cast the datetime object into date string using 'datestr(<your_datetime_object>,31)', before passing it to one of the insert functionalities.

Here is the documentation link with more details: http://www.mathworks.com/help/database/ug/datainsert.html

Apologies for this inconvenience. We will take a note of your inquiry and consider support for datetime objects in Database Toolbox for a future MATLAB release.

Rikin

[Matt]

Thank you for your response, I'm really surprised that functionality does not exist being how often dates/times are used.

I look forward to that being implemented in a future release with how much easier datetimes have become with the new timetables.

[Brian]

Has this functionality been implemented yet? I've encountered this issue and have resorted to using an 'INSERT INTO' query with 'CAST(char(datetime('now')) AS datetime)' function instead of just being able to write a table that includes a datetime using sqlwrite.

Answer 2

[Dauren]

Hi Matt,

you may try to write your datetime as table in sql. But table will contain your dates as char variables.

conn=database('YOUR_DATABASE','','');
%your datetime
date0=datetime('today','Format','dd-MM-yyyy');

%convert datetime to number and create sequence of dates
date1=datenum(date0); 
date2=date1:date1+5;

%convert dates to char variables
date=datestr(date2,'dd-mm-yyyy');

%Write results in table
T=table(date)

%It is easier to write date in sql as table in Matlab
tablename='SQL_table1';
sqlwrite(conn,tablename,T);

Then when you read the data from Sql you may convert char dates back to datetime

%Read data from sql 
time2=sqlread(conn,'SQL_table1');

%remove curly brackets
Date1=cell2mat(time2.date)

%convert back to datetime
Date0=datetime(Date1,'Format','dd-mm-yyyy')

I hope that helps.

Good luck!

Duka.