The whole point of this is to make it as easy as possible for the user.
With other backends the database handles the types but with sqlite
the user has to get involved.
It is not a matter of storage. It is a matter of maintaining the type
information
on the database side and passing the type information back to R.
Perhaps an example would help. Consider this. Here R passes the
type information to the H2 database backend and H2 passes it back to R
so that the output is correctly typed. This works as desired.
library(RH2) # load H2 driver and H2 database
library(sqldf) # load package
DF <- data.frame(d = as.Date("2000-01-01")) # create data.frame
with one column d
sqldf("select d, d + 1 as nextDay from DF")
## d nextDay
## 1 2000-01-01 2000-01-02
Now if we switch from H2 to sqlite that would have not have worked.
Any of these workaournds
are currently implemented and work but are not optimal.
library(sqldf) # if no driver loaded it assumes sqlite
# Alternative 1
# method = "Date" means all returned numbers are regarded as Date
sqldf("select d, d + 1 as nextDay from DF", method = "Date")
## d nextDay
## 1 2000-01-01 2000-01-02
# Alternative 2
# this is similar to what Simon suggested.
# method = "name__class" means use the column name suffix
# to determine the type
sqldf("select d as d__Date, d + 1 as nextDay__Date from DF", method
= "name__class")
## d nextDay
## 1 2000-01-01 2000-01-02
# Alternative 3
# convert it manually. It deduces that d is Date because an input
# column of the same name exists as Date but that is not foolproof
# and does not, for example, work for the nextDay column so we
# convert it manually
out <- sqldf("select d, d + 1 as nextDay from DF")
out$nextDay <- as.Date(out$nextDay, origin = "1970-01-01")
out
## d nextDay
## 1 2000-01-01 2000-01-02
On Sun, Aug 11, 2019 at 7:16 PM Keith Medcalf <kmedc...@dessus.com> wrote:
>
>
> On Sunday, 11 August, 2019 07:45, 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.
>
> SQLite3 can store integers and doubles. Seems to me this is quite sufficient
> for storing a "number of seconds" since the Unix Epoch or "number of days"
> since the Unix epoch. What exactly is the problem? An IEEE-754 double
> precision floating point number is certainly capable of storing all offsets
> from the Unix Epoch (either as a days or seconds offset) with far more
> precision that a human is likely able to discern (or with more precision than
> the accuracy of most atomic clocks, for that matter).
>
> >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.
>
> Why not? The column type declaration in the table is merely an "arbitrary
> string", and the returned column names from a select are merely "arbitrary
> strings". It seems like an "application deficiency" that it cannot set and
> retrieve "arbitrary metadata" for its own internal use (such as providing
> column type declarations in the CREATE TABLE or additional metadata in the
> column name (in select statements). Many other "applications" do so without
> difficulty (for example the default sqlite3 wrapper in Python). Are the R
> programmers somehow particularly deficient in this regard?
>
> I quite often store "timestamps" using application dependent epochs and
> offsets without difficulty (for example, the number of one-minute intervals
> since the Unix epoch). Given that just about every programming system and
> language ever invented seems to store datetime data as some type of offset
> from some epoch, and each of them different, does not seem to bother
> interoperability in the least. Once you know the Epoch and Offset interval,
> conversion is rather simple arithmetic that most children learned in primary
> school.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
