Actually sqldf has multiple heuristics and the one you suggested is already one of them (except for minor differences in syntax) but this has the disadvantage that the user must specify classes whereas if the user simply uses any of the other backends they don't have to.
On Sun, Aug 11, 2019 at 10:52 AM Simon Slavin <slav...@bigfraud.org> wrote: > > On 11 Aug 2019, at 2:45pm, Gabor Grothendieck <ggrothendi...@gmail.com> wrote: > > > 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. > > Add a new heuristic that if the column name ends in '_Date' or '_ > POSIXct' then the value should be converted. When R creates a new SQLite > table, it should append '_Date' or '_POSIXct' to the column name it chooses > for classes of those types. Existing heuristics should continue to work. > > I do understand the value of having date/time types in SQLite, but it is not > easy to do while retaining backward compatibility. It'll have to wait for > SQLite4 or something. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
