SQL Server jdbc - Time, milliseconds and microseconds.

Hi,

I have a sql query and everything works fine, but matlab during the import lose all the numbers after the seconds. Inside the DB I have 7 numbers after the seconds that are complitely lost in Matlab.

SQL --> 07:55:04.1621280

MATLAB--> 07:55:04

I have attached two screen shots, one from the DB and one frome the "CURS" in ML.

Could anyone give me any suggestion on how can I keep all the microseconds?

This page gave me some information but is not usefull for SQL:

http://blogs.mathworks.com/loren/2015/02/12/introduction-to-the-new-matlab-data-types-for-dates-and-time/

Many thanks

s

Answer 1

[the cyclist]

Are you sure it is not just an issue with how the number is being displayed? It may have the full precision.

I generally return data in cell array format, and have no problem. Perhaps try that?

[the cyclist]

Moving OP's "answer" here, since it was actually a comment:

----

Hi Cyclist,

Yep that could be. Any suggestion on how to check that?

Thanks

s

[the cyclist]

If the value is stored in the variable x, try

datestr(x,'yyyy-mm-dd HH:MM:SS.FFF')

to see if the millisecond accuracy is retained.

[Simone Cavallarin]

Unfortunately the .FFF are all zeros, the data is gone. But thanks anyway! I'm working now into the SQL query to import the microseconds into another column. FORMAT("TABLENAME".TIME,'fffffff') as MICROSEC

It looks like I'm loading the data but is slow, and i need to merge the time with the MICROSEC somehow.

[Manuel Dias]

Hi i stumbled at the same difficulty and i found one way to pass it. Use

Select time_collumm, DATEPART( millisecond,time_collum) as milliseconds from table_name

This will import to matlab a table with 2 collums. One with hh:mm:ss and the other with milliseconds. Now just convert the milliseconds to char and then add to the time part.

If you need more information about this solution you can find it here: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

[the cyclist]

Interesting idea. Along these lines, you could also perform the character concatenation within the SQL query itself, and just return the one column you need. (In postgres you concatenate with .)