On 8/4/2011 11:35 AM, Sean Hammond wrote:
> 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.)That's irrelevant. You are storing strings. SQLite doesn't have a dedicated timestamp format. See http://sqlite.org/datatype3.html > 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" datetime(x) is just a shorthand for strftime('%Y-%m-%d %H:%M:%S', x) > 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. Where would non-zero milliseconds come from? They are not stored with the data. > Am I correct in thinking that sqlite3 stores datetime('now') as a string > without milliseconds? To be precise, datetime('now') returns a string in a format that doesn't include milliseconds. You then store this string in the table. > Are the resolutions of sqlite's date and time > functions explicitly documented anywhere? http://sqlite.org/lang_datefunc.html > 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')); That's one way to do it, yes. > then the results from SELECT queries will contain fractions of a second. Of course. You get back out exactly what you put in. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
