Convert Sqlite Unix Epoch in milliseconds to Datetime YYYY-MM-DD HH:MM:SS.SSS

Question

I´ve been searching for quite some time for a solution to this problem and probably some could help me or point me in the right direction. I did read all the Q&A related to this here and still haven't found the solution.

I have a c# program that writes into an SQLite database. It writes the events and stores time in milliseconds (Unix Epoch in ms - 1511737200042/ 1511737200sec part 042 ms part).

I've tried:

SELECT datetime(time/1000,'unixepoch') from table1;

= 2017-11-26 23:00:00

which is equivalent to

SELECT strftime('%d-%m-%Y %H:%M:%f', datetime(time/1000, 'unixepoch')) FROM table1;

=26-11-2017 23:00:00.000

But the results don't have the ms granularity.

I see that its because I'm dividing time by 1000 and then forcing the fraction which by that time is inexistent. But if I change that division I get completely different times.

Nowhere could I find a solution to this problem.

I'm now thinking if I should store the millisecond (3 last digits) in another column and then join the two columns.

Could anyone please provide any pointers?

Thank you Kind Regards,

PS: Importing into SQL using SQL Server Import and Export Wizard "time" an Integer NOT NULL 1511737200042 transforms to 2147483647 a 10digit number. Also in PowerBi.

Answer 1

All date functions accept the same date formats and modifiers, so you can simply remove the nested datetime call. And you need to do a floating-point division to preserve the milliseconds:

SELECT strftime('%d-%m-%Y %H:%M:%f', time/1000.0, 'unixepoch') FROM table1;