CODEWITHSUNDEEP
rrsqlite
Mirko Gagliardi
Mirko Gagliardi

Reputation: 69

RSQLite - dbWriteTable - field.type - How to get proper Date format?

RSQLite::dbWriteTable function, properly pass Dates from df to db.

I have some date fields on my df, and when I try to write the table on an SQLite db using the aforementioned function and setting field.types = c(col1 = "Date", col2 = "Date") it writes those date fields as a number instead of date, e.g. :

"2018-12-01" becomes 17866

Here's my complete code:

dbWriteTable(db, "table", df, overwrite = T, append = F, 
          field.types = c(Col1 = "Date", Col2  = "Date"))

This actually writes the "table" on the SQLite db with proper Date format for such columns, but the values inside the cells are not dates, they remain numbers such as 17866.

Here I found someone suggesting a workaround, transforming the date as.character.

Is there a proper way to pass a date value to an SQLite db?

Thank you in advance

Upvotes: 1

Views: 1601

Answers (3)

sactyr
sactyr

Reputation: 429

Just wanted to say @koinobori 's solution actually works, despite it being downvoted. Full solution:

# Create a db connection and mtcars db
conn <- dbConnect(
  drv = RSQLite::SQLite()
  ,"mtcars.db"
  ,extended_types = TRUE
)

# Create data with date column
mtcars$dt <- Sys.Date()

# Create a table in mtcars.db called mtcars_tbl
dbWriteTable(
  conn = conn
  ,name = "mtcars_tbl"
  ,value = mtcars
)

df <- dbGetQuery(
  conn = conn
  ,statement = "
  SELECT *
  FROM mtcars_tbl
  "
)

str(df)
'data.frame':   32 obs. of  12 variables:
 $ mpg : num  21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ...
 $ cyl : num  6 6 4 6 8 6 8 4 4 6 ...
 $ disp: num  160 160 108 258 360 ...
 $ hp  : num  110 110 93 110 175 105 245 62 95 123 ...
 $ drat: num  3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ...
 $ wt  : num  2.62 2.88 2.32 3.21 3.44 ...
 $ qsec: num  16.5 17 18.6 19.4 17 ...
 $ vs  : num  0 0 1 1 0 1 0 1 1 1 ...
 $ am  : num  1 1 1 0 0 0 0 0 0 0 ...
 $ gear: num  4 4 4 3 3 3 3 4 4 4 ...
 $ carb: num  4 4 1 1 2 1 4 2 2 4 ...
 $ dt  : Date, format: "2024-05-02" "2024-05-02" "2024-05-02" "2024-05-02" ...

Upvotes: 1

koinobori
koinobori

Reputation: 21

An "extended_types" argument may work at DBconnection.

db <- DBI::dbConnect(RSQLite::SQLite(),
                     dbname = "xxx",
                     extended_types = T) # this point

Upvotes: 1

Mirko Gagliardi
Mirko Gagliardi

Reputation: 69

Reviewing now, I never actually found a way to do this as described on my question. In the end I went for the workaround that I mentioned in my question:

transform all the date variables in text before writing on the SQLite DB.

Upvotes: 1

Related Questions