1

I have a timestamp in epoch time with nanoseconds, like this: 1634713905326784000 I am using DB Browser for SQLite to convert this into a timestamp, like this:

STRFTIME('%Y-%m-%d %H:%M:%f', timestamp/1000000000,'unixepoch') as timestamp

Since I am dividing by 1000000000 I just get .000 in the last part of the timestamp, like this:

2021-10-20 07:11:45.000
2021-10-20 07:11:45.000
2021-10-20 07:11:45.000
2021-10-20 07:11:45.000

It is possible to extract the nanoseconds, like this:

CAST ((timestamp % (timestamp/1000000000))/1000000 as NUMERIC) as nsec

Which will give me a new column with only nanoseconds

326
372
386
407
430

I would like to get the complete time, including decimals in one column so that when I plot the data I do not get them lumped up at the start of each second, like this:

2021-10-20 07:11:45.326
2021-10-20 07:11:45.372
2021-10-20 07:11:45.386
2021-10-20 07:11:45.407
2021-10-20 07:11:45.430

Is this possible? Thanks

petterssonm
  • 13
  • 3
  • `.326` is *milli*seconds, not nanoseconds. Divide only by `1000000` to get an epoch in milliseconds. The Unix Epoch SQLite understands is in seconds though, not milliseconds or nanoseconds – Panagiotis Kanavos Nov 03 '21 at 10:57
  • Yes, I meant milliseconds for the .326 part.. But the timestamp that I have in my database is in nanoseconds I believe. Anyway, I don't need nanosecond resolution, milliseconds is good enough. Is it possible somehow to fill up those last three digits with milliseconds instead of just .000? – petterssonm Nov 03 '21 at 15:08
  • Wouldn't something like `SELECT STRFTIME('%Y-%m-%d %H:%M:%f', CAST(timestamp AS float) / 1e9,'unixepoch') AS timestamp` (or 1e6 if you have ms) work? – Anon Coward Nov 03 '21 at 18:51
  • Yes! That seemed to do the trick!! Thank you!! If you post it as an answer instead of as a comment I can mark it as solution and vote up etc. etc.. – petterssonm Nov 04 '21 at 08:07

1 Answers1

2

You were very close with your use of STRFTIME. It will convert the value it gets to a datetime, but since you were using integer math, then it didn't get the fractional time.

SELECT 
    STRFTIME('%Y-%m-%d %H:%M:%f', CAST(timestamp AS float) / 1e9,'unixepoch') AS timestamp

This will do what you want. First convert the timestamp to a float value, then divide by 109 to convert the value to a unix epoch.

Anon Coward
  • 9,784
  • 3
  • 26
  • 37