Anyone has an idea on a way to convert a windows 64bit file time to an accurately human readable display format? I unfortunately cannot load extensions for this. It should also be possible to convert back to exactly the original 64bit number:

I am stuck around queries like these, not correct though:

select datetime(132061681346589253 / 10000000 + strftime('%s', '1601-01-01'), 'unixepoch', 'localtime') as NanosecondReadableDateTime;

2019-06-28 06:02:14

Which lacks the nanosecond part and even this might be incorrect...

Philip

forumpost/4a658e3adca5442b might be of interest

Thanks, got that post when searching for just nanosecond and it is helping!

My problem is that nanosecond part, actually the windows file time is 100ns parts since 1601.

I am loosing brain cells by the nanoseconds but will probably succeed once reaching the unix microseconds!

And then I have to be able to SQL it back to the number representation...

Just format the fractional seconds separately and append them:

    datetime(ntfstime/10000000-11644473600,'unixepoch','localtime') ||
        printf('.%.7u',ntfstime%10000000)

In the other direction, extract a trailing substring starting with '.' and convert that separately:

    10000000*(11644473600+cast(strftime('%s',timetext,'utc') as integer))+
        case when instr(timetext,'.') then
            cast(1e7*cast('0'||substr(timetext,instr(timetext,'.')) as real) as integer)
        else
            0
        end

Why not just store the 64-bit number directly?

Why on earth would you want to convert something that starts out in format A to format B for storage just so you can convert it back again? Why not just store the original value intact?

Why do you want to store the presentation format? It would be perspicacious to store the actual data value and keep "presentation" to where it is needed -- that is -- in the user facing application that needs to "present" the value to the user. This also has the advantage that when the luser wants the presentation format changed, then it is just a change to the "present to the user" function. (THough, if it were me, I would ignore the luser and store the origin value properly and convert on presentation whether or not that is what they wanted however I would certainly bill them in accordance with their level of stupidity when they request that the presentation format change, even though I would make the cvhange to the "display for presentation" function in just a few sonds, they would pay as if I had done what they requested -- let the stupid ***** pay for their own stupidity.)

Assuming that "hunstime" refers to the hunstime value then this will return what you want (as in convert the stored "hunstime" value to presentation format).

Since you already have the original value there is no need for obverse conversion -- simply use the value you already have stored.

select strftime('%Y-%m-%d %H:%M:%S', trunc(hunstime() / 1e7, 'unixepoch') - 11644473600) || printf('.%07d', hunstime() % 1e7);

Notes:
Replace "hunstime()" with the 64-bit huns count -- it must not change between the two uses of the value.
11644473600 is the number of seconds between the posix/unix epoch and the ANSI epoch.
You can use cast(hunstime() / 1e7 as integer) in place of trunc(hunstime() / 1e7) if you wish. What is important is that the value is truncated (not rounded).

You could also store the presentation format when the underlying value changes as a stored computed column.

create table t
(
    hunstime        integer not null,
    presentation    text as (strftime('%Y-%m-%d %H:%M:%S', trunc(hunstime / 1e7) - 11644473600, 'unixepoch') || printf('.%07d', hunstime % 1e7)) stored
);

sqlite> create table t
   ...> (
   ...>     hunstime        integer not null,
   ...>     presentation    text as (strftime('%Y-%m-%d %H:%M:%S', trunc(hunstime / 1e7) - 11644473600, 'unixepoch') || printf('.%07d', hunstime % 1e7)) stored
   ...> );
sqlite> insert into t values (hunstime());
sqlite> insert into t values (hunstime());
sqlite> insert into t values (hunstime());
sqlite> insert into t values (hunstime());
sqlite> insert into t values (hunstime());
sqlite> select * from t;
┌────────────────────┬─────────────────────────────┐
│      hunstime      │        presentation         │
├────────────────────┼─────────────────────────────┤
│ 132860605891704458 │ 2022-01-07 20:23:09.1704458 │
│ 132860606985071854 │ 2022-01-07 20:24:58.5071854 │
│ 132860607015158963 │ 2022-01-07 20:25:01.5158963 │
│ 132860607037154149 │ 2022-01-07 20:25:03.7154149 │
│ 132860607052201525 │ 2022-01-07 20:25:05.2201525 │
└────────────────────┴─────────────────────────────┘
sqlite>

Which lacks the nanosecond part and even this might be incorrect...

Keep in mind that neither Windows, Linux or BSD are real time operation systems. You can't count at such precision time on these system. If you find some computer that still has LPT port or even dedicated PCI card where you can flip hardwired GPIO (Raspberry Pi in the same category) and will try to simply flip 0/1 in a loop while monitoring output with oscilloscope, then you will find that meander isn't stable and changing its duty cycle and frequency randomly (not a lot, but it has jitter). This is due to kernel's scheduler don't guarantied that it will give particular process exact time frame on periodic basis. Kernel might pick some hardware interruption and freeze other processes while taking care about interrupt request (especially NMI interrupt).

If you application processing events that critically defined to time constraints on such high precision (nanoseconds) then you have to use real time operation system such as RTOS or use computer as a control center and offload critical time based operations on external microcontroller.