SQL datetime query - is there a faster way?

2020 9 30
1 个回答
回答已采纳
更新时间：2020 10 7
8 次查看（30 天）

0 个投票

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..?

请先登录，再回答此问题。

 采纳的回答

Kojiro Saito
Kojiro Saito 2020-10-1

1 个投票

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);

3 个评论
显示 1更早的评论 隐藏 1更早的评论

Oliver Warlow
Oliver Warlow 2020-10-5
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
Kojiro Saito 2020-10-6
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
Oliver Warlow 2020-10-7
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.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息

产品

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by