[R-sig-DB] RSQLite and Date

[Dirk Eddelbuettel]

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.

Dirk

-- 
Hell, there are no rules here - we're trying to accomplish something. 
                                                  -- Thomas A. Edison