How to import text to timetable with ntsf timestamp

Cannot figure out the syntax and/or import options to change to allow reading of text for ntsf time format into timetable.

[dpb]

https://docs.microsoft.com/en-us/windows/desktop/sysinfo/file-times on the assumption NTSF --> NTFS If not, never heard of it.

[Chris]

It is Windows file time. I had not heard of it either but 'ntfs' is what it is called in MatLabs datetime format documentation. I can convert a numeric array to it, but have not found the syntax to import it from a file into a timetable without importing the arrays individually and then building the timetable.

[dpb]

NTFS ~= NTSF was the Q...

Answer 1

[Peter Perkins]

In recent versions (since R2018a? or thereabouts):

>> help datetime
[snip]
D = datetime(X,'ConvertFrom',TYPE) converts the numeric values in X to
    a datetime array D. D is the same size as X. TYPE specifies the type of
    values contained in X, and is one of the following:
[snip]
       'ntfs'                The number of 100ns "clock ticks" since 1-Jan-1601 00:00:00 UTC
[snip]
    D = datetime(X,'ConvertFrom','epochtime','Epoch',EPOCH) converts the
    numeric values in X to a datetime array D. X contains the number of
    seconds before or since the epoch. EPOCH is a scalar datetime or a
    date/time character vector or string scalar, representing the epoch 
    time. The default epoch is 1-Jan-1970 00:00:00 UTC.

    D = datetime(X,'ConvertFrom','epochtime','Epoch',EPOCH,'TicksPerSecond',N)
    converts the values in X from the numeric time representation specified
    by EPOCH and N, to a datetime array D. X is a numeric array
    representing time as the number of "clock ticks" before or since the
    epoch time. EPOCH is a scalar datetime or a date/time character vector
    or scalar string that specifies the epoch time. N is a scalar integer
    that specifies how many clock ticks there are per second.

[Chris]

I have been able to convert an NTSF formated time variable to MatLAB datetime as you describe. (And here it only accepts a number formated as uint64.) What I am having trouble with is doing this conversion while importing a text file as a timetable. I would expect the import to be able to handle this instead of me having to import the arrays, convert the time variable, and then assemble the timetable from the imported arrays. This will get tedious as I have to import several such tables. Maybe I can make a function/script that abstracts it sufficiently but I want to avoid having to do the manipulation case-by-case for each data file. I suspect my issue is just a matter of finding the correct import option. Here is a sample of my most recent attempt:

DataTTImportOptions = detectImportOptions((DataFileName,'FileType','text');
DataTTImportOptions.VariableTypes = ["datetime", "double", "double", "double", "double", "double", "double"];
DataTTImportOptions = setvaroptsDataTTImportOptions.VariableTypes 1, "InputFormat", "ntfs");
DataTT = readtimetable(DataFileName,ImportOptions);

This is apparently NOT what that "InputFormat" is for as it throws the error:

Error using matlab.io.internal.shared.DatetimeVarOptsInputs/setInputFormat (line 104)
The format'ntfs' contains an unsupported symbol: 'n'. See the datetime.Format property for a complete description of the
identifiers used in datetime formats.

[dpb]

Indeed, as implemented the 'InputFormat' option is to scan text time strings, not do the conversion from UTC/NTFS.

As noted, write a specific function that reads the time column as numeric, does the conversion and builds the table before returning.

Once done, it would appear to the end user the same as if the readtimetable routine did it itself.

Possibly a worthwhile enhancement request...

[Chris]

I eventually came up with this compact solution:

ntfs2dt = @(x)(datetime(uint64(x),'ConvertFrom','ntfs'));

DataTT = table2timetable(convertvars(readtable(DataFileName,ImportOptions),{'TimeVarName'},ntfs2dt));

[dpb]

Nicely done...but why don't you set the appropriate 'VariableType' for the NTFS datestamp to uint64 in the import options object to do the type cast on read? As Peter illustrated you're losing precision in the input process as there aren't sufficient bits in a double to store a 64-bit int.

[Chris]

For maximum precision I concur with the need to import as uint64. I had not noticed because in my application the time variable is timestamps on data sampled up to 20 kHz. 50 us vs 100 ns count would require losing more than 9 bits of precision to notice. For my current application it makes no significant difference other than the extra step of specifying the import option. But I agree it is in general good practice.

[dpb]

But, since you're writing generic code and creating the import object anyways, why not go to the earliest source possible? Then the code is generic and usable in the future even if the timestamp granularity changes. And then there's the secondary advantage the anonymous function is simpler without the cast operation.

I don't know as I've not tried to run a timing test, but my first guess is that performance would be better with the translation on input as opposed to in the function as well, but that's conjecture, granted.

You seem to have gone to considerable pains to get this far, why not complete the job? <VBG>

Answer 2

[Peter Perkins]

"it only accepts a number formated as uint64"

NTFS timestamps are defined as 64bit numbers. Anything smaller than that and you get round-off. For contemporary timestamps, if you put an NTFS timestamp into a double, you'll only get about 1microsec resolution.

>> (seconds(datetime('now') - datetime(1601,1,1))) / flintmax
ans =
    1.466e-06