It's really useful that SQLite now supports window operations as that was
one of the key features that R users needed to do many manipulations
using SQLite.
From the perspective of the R language there is really one
particularly key feature left that prevents some users from easily
using SQLite in many cases. That is the lack of date and time types.
From the perspective of the sqldf package in R, the user writes:
sqldf("select ...whatever...")
and sqldf creates an empty sqlite data base, looks for all table names
in the select statement, generates create table statements for them,
uploads the tables to the new database, runs the statement, downloads
the result and deletes the database. (This package uses a lower level
R driver package for sqlite which may also be used directly.)
R supports Date and POSIXct (date/time) classes which are represented
internally as days and seconds since the UNIX Epoch respectively;
however, due to the class it knows to display and manipulate them as
dates and datetimes rather than numbers.
If sqldf sends a Date or POSIXct to SQLite then it is sent as a number (days or
seconds since the UNIX Epoch) but when it is sent back it cannot know that
that number is supposed to represent a date or datetime. There are some
default heuristics (if any of the input tables have a column name the same name
as an output column name then such output columns are automatically
converted) but this is far from foolproof.
This is not a problem for the other backend databases that are
supported since those backends have true date and datetime types so
when R receives such objects it knows to convert them to R's similar types.
Currently SQLite is the default backend but I normally recommend that users
switch to the java based H2 backend if they are doing a lot of date and datetime
processing for the above reason. A lot of data analysis does involve
dates and date times so this covers a lot of applications.
If SQLite were to support true date and datetime types, as do other databases,
and not just functions which handle numbers as if they were dates or
datetimes this problem would be addressed so this is a key remaining feature
that I think SQLite needs.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
