[R-sig-DB] RSQLite and Date
Seth Falcon
@|@|con @end|ng |rom |hcrc@org
Thu May 17 18:17:24 CEST 2007
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
More information about the R-sig-DB
mailing list