How to prevent Milliseconds from disappearing during Import Data from Database?

[#1]

Hello, 

TLDR: Is there a way to keep milliseconds when data is imported from a database? Import JSON, timestamp milliseconds all turn into 0's. JMP 16.2/17.0

I currently have a JSL script that pulls json data from a database

 ex ->  

Data Table(open database(dsn_string, sql_string, table_name, Invisible(1))); 

This script has been working with no issues except that the granularity of the timestamp column are missing the milliseconds.

In the original JSON file, the timestamp have milliseconds ("TIME_STAMP":"2022\/08\/10 17:00:37.130") but in JMP, the timestamp no longer has milliseconds. Even if I edit the Column Info to have more decimals positions, milliseconds are all 0's.

Missing the milliseconds is causing analysis issues due to rows being sorted incorrectly. I really need the granularity of milliseconds in my data.

Does anyone know how to solve this issue of why milliseconds disappear during data import?

Any clues to prevent this from occurring?

Thank you all in advance for the help!!!

[#2]

I suspect the fix will be on the database end, not the JMP end, for example, https://database.guide/how-to-return-fractional-seconds-from-a-datetime-value-in-oracle/ , possibly specific to the database brand.

As a work-around, you might export CSV from the database, though it might need the same fix.

Imports the fractional seconds OK

edit: that might be less than clear; "on the database end" still means editing the query to make the database send the data.

[#3]

Hi Craige_Hales.

Thanks for the suggestion.
Do you mean that, in my case, the query to import the JSON data needs to be edited to account for milliseconds? I've checked the JSON data and it for sure has the necessary milliseconds, but I have not been able to find a solution to import correctly into JMP. Do you have any pointers?

note: I work with large datasets and pull 100's of JSON files that are all automated with JSL scripts. It would less then ideal to import into a csv then import that into JMP every time.

[#4]

Do you mean that, in my case, the query to import the JSON data needs to be edited to account for milliseconds?

that is what I was describing. I believe the link might be helpful. I'm not more than an occasional SQL user; others will know more. From the link it is pretty clear that all SQL are not the same. You should add which SQL variant you are dealing with. Though it seems likely that the SQL to convert a date to a string before sending it to JMP might be fairly standard.

edit: re-reading...I'm confused. where is JSON coming from? Are you reading JSON with JMP? or are you using JMP's SQL support? Probably need to see a bit more of what you are doing.