CODEWITHSUNDEEP
sqlitedate
betaros
betaros

Reputation: 602

SQLite add milliseconds to time

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?

Upvotes: 5

Views: 3107

Answers (1)

Philippe Banwarth
Philippe Banwarth

Reputation: 17725

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.

Upvotes: 6

Related Questions