Filter löschen
Filter löschen

SQL datetime query - is there a faster way?

30 Ansichten (letzte 30 Tage)
Oliver Warlow
Oliver Warlow am 30 Sep. 2020
Kommentiert: Oliver Warlow am 7 Okt. 2020
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..?

Akzeptierte Antwort

Kojiro Saito
Kojiro Saito am 1 Okt. 2020
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
data = fetch(conn, sqlquery, opts, 'MaxRows', 10);
  3 Kommentare
Kojiro Saito
Kojiro Saito am 6 Okt. 2020
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 am 7 Okt. 2020
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.

Melden Sie sich an, um zu kommentieren.

Weitere Antworten (0)


Community Treasure Hunt

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

Start Hunting!

Translated by