[R-sig-DB] RSQLite and Date

[Gabor Grothendieck]

On 5/17/07, Seth Falcon <sfalcon using fhcrc.org> wrote:
> Dirk Eddelbuettel <edd using debian.org> writes:
>
> > On 17 May 2007 at 10:57, Gabor Grothendieck wrote:
> > | Below dbDataType reports that "Date" class is represented as TEXT in the
> > | data base.  When I write and read back a data frame it does come back
> > | as "character" but as the character representation of the number of days
> > | since the Epoch.  Is that how its supposed to work?
> > |
> > | Are there any facilities that the user can control to specify how
> > | given classes are converted back and forth?
> > |
> > | > m <- dbDriver("SQLite")
> > | > dbDataType(m, Sys.Date())
> > | [1] "TEXT"
> > | > con <- dbConnect(m, dbname=":memory:")
> > | > DF <- data.frame(Date = Sys.Date() + 0:3, Value = 10:13)
> > | > dbWriteTable(con, "DF", DF)
> > | [1] TRUE
> > | > str(dbReadTable(con, "DF"))
> > | 'data.frame':   4 obs. of  2 variables:
> > |  $ Date__1 : chr  "13650.0" "13651.0" "13652.0" "13653.0"
> > |  $ Value__1: int  10 11 12 13
> >
> > Great topic.
> >
> > I once had a similar issue with RdbiPgSQL and found (from looking at
> > dbiPgSQL/R/types.R as well as the Postgres docs) that all I needed was to add
> >
> >        rpgsql.cast.values.1114 <- function(x) {
> >               as.POSIXct(strptime(x, "%a %b %d %H:%M:%OS %Y")) };
> >
> > and I'd get POSIXct's mapped.
> >
> > It would be nice if we get the maximum number of types mapped for the maximum
> > number of db backends...  And it would of course also be nice if one day we
> > get a volunteer to bring RdbiPgSQL into the proper DBI world.  Maybe next
> > year's Google SoC.
>
> Patches welcome.  But the date problem is quite possibly trickier with
> SQLite since there is no date type.  So I don't know where that
> information should get stored/specified.

There is no 100% clean solution I can think of but one idea would be
to translate
the R class into an SQLite class + an SQL constraint.  So "Date" class
might be translated into TEXT  plus a constraint that the contents of the column
be of the form yyyy-mm-dd.  In translating back it would have a table of
constraints so that it could translate back properly.