SQL datetime query - is there a faster way?

I am reading date from some SQL tables (in this case MYSQL) which are timestamped with the datetime format in MYSQL.

When I read these using a fetch query from matlab the timestamp is returned as a character array.

Ultimately I need to convert this to a matlab datetime format as this is the best efficiency way for using the data in all my calcualtions and of course uses less space. However the conversion using datetime is a significant (I am talking about millions or rows here).

Any ideas on efficiency savings? ideally I would like to be able to import directly to the datetime format if possible..?

Answer 1

[Kojiro Saito]

As of JDBC and ODBC connections, MySQL's datetime and timestamp will be imported as char. You can change the import options by databaseImportOptions (R2018b or later).

datasourceName = "mysqlJdbc"; % The name of JDBC datasource for MySQL
username = "USERNAME";
password = "PASSWORD";
conn = database(datasourceName, username, password);

opts = databaseImportOptions(conn, tablename);
columnNames = {'col1', 'col2'}; % The column names you want to change the import options
opts = setoptions(opts, columnNames, 'Type', 'datetime'); % Change from char to datetime
sqlquery = "SELECT * FROM YOUR_TABLENAME";
data = fetch(conn, sqlquery, opts, 'MaxRows', 10);

[Oliver Warlow]

Hi Kojiro, thanks for your respone. That appears to work, however I now cannot get the syntax to work when I want to select specific elements from the table e.g.

dwhQuery = "SELECT Timestamp, Value FROM MY_TABLENAME WHERE DeviceId = 17603 AND SignalId = 1 AND Timestamp >= '2020-01-01 00:00:00' AND Timestamp <= '2020-09-30 00:00:00'"
QReturn = fetch(dwhConn, dwhQuery, opts, 'MaxRows', 10);

Will not work when I use the options command, but exlude it from the fetch statment and it executes fine. Am I missing something here? Is there a way round this?

Thanks

Oli.

[Kojiro Saito]

It seems that opts = databaseImportOptions(conn, tablename) allows fetch(conn, sqlquery, opts) where sqlquery is SELECT * FROM tablename.

I've read the document (databaseImportOptions) and found that by changing the source in databaseImportOptions, we can get the expected return.

So, possibly you code should be as follows.

dwhQuery = "SELECT Timestamp, Value FROM MY_TABLENAME WHERE DeviceId = 17603 AND SignalId = 1 AND Timestamp >= '2020-01-01 00:00:00' AND Timestamp <= '2020-09-30 00:00:00'"
opts = databaseImportOptions(conn, dwhQuery);
columnNames = {'Timestamp'};
opts = setoptions(opts, columnNames, 'Type', 'datetime');
QReturn = fetch(dwhConn, dwhQuery, opts, 'MaxRows', 10);

[Oliver Warlow]

Hi Kojiro, again thansk for your repsonse. Yes it now seems to work as long as I apply the expected query string to the options.

However - the result is what I was worried about and that is that asking specifying to return "datetime" is significantly slower. So slow in fact that it is slower than converting the char to the datetime after the SQL query so doesn't really help with my efficiency problem as much as I had hoped.

Oli.