SQL Query to convert unixepoch time in nanoseconds to human readable time-CodePudding

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

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

CodePudding user response：

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 10⁹ to convert the value to a unix epoch.