[R-sig-DB] RSQLite and Date

[Seth Falcon]

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.

+ seth

-- 
Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center
http://bioconductor.org