How to import a complete Time collumn from a database?

I'm trying to import to MatLab a collumn of a table of a database (microsoft sql server). The collumn type is Time(3). The problem i am having is to import the complete collumn. Instead of receiving data of format ('HH:mm:ss:SSS') i am receiving ('HH:mm:ss'). I have checked the database and the data is in the correct format. The image is the content of database. And here's what i've done in matlab:

    conn =database('DBName','','','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://localhost:1433;database=DBName;rewriteBatchUpdates=true;instanceName=MSSQLSERVER;integratedSecurity=true;');
    selectQ='SELECT top 100 * FROM CanData  inner join Events On CanData.eventID=Events.eventsID where eventsID=9 AND canSystemID=1024 ORDER BY canTime DESC';
    curs=exec(conn,selectQ);
    curs=fetch(curs);
    curs.Data
close(conn);

Answer 1

[TARUN]

Hi @Manuel Dias,

When importing a TIME(3) column from SQL Server into MATLAB, the milliseconds may get truncated because MATLAB does not automatically preserve fractional seconds from SQL TIME types.

To resolve this, you can convert the canTime column to a string format directly in your SQL query using CONVERT.

Here's how you can update your query:

selectQ = 'SELECT TOP 100 CONVERT(VARCHAR(12), canTime, 114) AS canTime, * FROM CanData INNER JOIN Events ON CanData.eventID=Events.eventsID WHERE eventsID=9 AND canSystemID=1024 ORDER BY canTime DESC';

This ensures that canTime is returned as a string in the 'HH:mm:ss:fff' format, preserving the milliseconds.

In MATLAB, you can then convert it to a datetime format with milliseconds using:

datetime(curs.Data(:, 'canTime'), 'InputFormat', 'HH:mm:ss.SSS')

This approach keeps your time data intact and usable for further processing or analysis.

Feel free to refer to MATLAB’s datetime function for more options: https://www.mathworks.com/help/matlab/ref/datetime.html

CONVERT function: https://www.w3schools.com/sql/func_sqlserver_convert.asp