SQLite add milliseconds to time

Question

I have a sqlite database where I want to add 84 seconds to each time filed. The time is formatted like this:

yyyy-MM-dd hh:mm:ss:zzz

2017-12-15 11:50:12.132

I've tried to modify the time with

UPDATE sensordata
SET time=DATETIME(time, '+84.000 seconds')

This adds 84 seconds correctly, but it deletes the milliseconds:

2017-12-15 11:51:36

How can I add the seconds and still have the milliseconds?

Answer 1

The datetime function does not format the fractional part.

You can use strftime() with the exact format you want :

(Edited to remove the redundant %S)

UPDATE sensordata
SET time=STRFTIME('%Y-%m-%d %H:%M:%f', time, '+84.000 seconds')

In fact datetime(...) is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), see Date And Time Functions for more details.