CODEWITHSUNDEEP
rsqlitedatelubridate
mickkk
mickkk

Reputation: 1192

How is a lubridate date object converted when storing a dataframe into a SQLite database in R?

Suppose you are storing a dataframe that contains dates in a sqlite database using the following code:

df <- data.frame(d = lubridate::as_date(1:30))
mydb <- DBI::dbConnect(RSQLite::SQLite(), "database.db")
DBI::dbWriteTable(mydb, "mytable", df)
DBI::dbDisconnect(mydb)

my understanding is that a conversion such as

as.numeric(df$d)

is performed before saving the data into the sqlite database. Is this correct? Why is the numeric conversion instead of the string conversion preferred?

Upvotes: 0

Views: 146

Answers (1)

Dirk is no longer here
Dirk is no longer here

Reputation: 368609

Briefly:

  1. There is no such thing as lubridate date object. There is a base R type Date you can create, _inter alias, with as.Date(). If you feel you must use another package so be it.

  2. The Date type is stored internally as a (floating point) number of (fractional) days since a start date (of 1970-01-01). So storing as a number is the most efficient way.

  3. Databases sometimes do, or do not, have native date types. MySQL didn't for many years; I can't recall where RSQLite is. It does not matter because ...

  4. Database access packages need converters from Date and Datetime along with native support in the database.

  5. We built this into RPostgreSQL a decade ago as PostgreSQL has it natively.

  6. If you want textual representation but either one (or both) of the interface or database layer cannot do Date types, then call format() first and store the character string. Every database knows how to do that. But you just lost the type information...

Upvotes: 1

Related Questions