Skip to content

RSQLite and Date

4 messages · Gabor Grothendieck, Dirk Eddelbuettel, Seth Falcon

Original
Gabor Grothendieck
# 
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?
[1] "TEXT"
[1] TRUE
'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
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
Seth Falcon
# 
Dirk Eddelbuettel <edd at debian.org> writes:
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
Gabor Grothendieck
#
On 5/17/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
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.