SQL datetime query - is there a faster way?

19 次查看（过去 30 天）
Oliver Warlow
Oliver Warlow 2020-9-30
评论： Oliver Warlow 2020-10-7
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
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更早的评论
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 个）

类别

在 Help Center 和 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