Hey, I've been recording timestamped log messages in sqlite3 by using 
datetime('now') in INSERT queries, e.g.:

INSERT INTO Logs (...,time) VALUES (...,datetime('now'));

(The time column has type DATETIME.)

I noticed that if I retrieve these rows with a SELECT query the 
datetimes only have per-second resolution, no fractions of a second, 
e.g.: "2011-07-31 16:04:48"

I tried to retrieve fractions of a second with a SELECT query containing 
a strftime with %f, e.g.:

SELECT strftime("%Y-%m-%d %H:%M:%f",time) FROM Logs;

but in the table returned all of the fractions of seconds are 000.

Am I correct in thinking that sqlite3 stores datetime('now') as a string 
without milliseconds? Are the resolutions of sqlite's date and time 
functions explicitly documented anywhere?

The solution seems to be to use strftime with %f in the INSERT query:

INSERT INTO Logs (...,time) VALUES (...,strftime('%Y-%m-%d 
%H:%M:%f','now'));

then the results from SELECT queries will contain fractions of a second.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to