RSQLite - Store date columns as character in SQLite

Question

Is there a way how I can store dates in R data frame as character strings in SQLite using RSQLite? Currently, date columns are stored as integers. I can probably cast all dates to strings before writing to SQLite, but as I need to write to SQLite from function where data frame is one of arguments I would prefer to avoid such transformation.

library('RSQLite')

df <- data.frame(
        x=1:3, 
        y=as.Date(c('2011-01-01','2011-03-03','2011-12-31'))
      )

df

# Create connection and temporary database
sqlite <- dbDriver("SQLite")
tmpdb  <- dbConnect(sqlite,"__this_is_temporary_db__.db")           

# Write data    
dbWriteTable(tmpdb,'df',df)

# We get integers from date
dbGetQuery(tmpdb,'select * from df')

dbDisconnect(tmpdb)      

# file.remove('__this_is_temporary_db__.db')

Answer 1

You should coerce to character. You can do it for all data.frame date columns using something like this:

ll <- lapply(df,function(x) 
     if(inherits (x,c('POSIXct','Date'))
       as.character(x)
     else x))
do.call(rbind.data.frame,ll)