What happens to date data in a database when using RSQLite?

Question

I've started working with RSQLite and dplyr to efficiently process large datasets. However, I haven't been able to reconcile how to get RSQLite to format dates or what best practices are here. The example below should illustrate where the process falls apart for me:

library(tidyverse)
library(RSQLite)

Data

Date is formatted appropriately

date=seq(as.Date("1910/1/1"), as.Date("1911/1/1"), "days")
x=rnorm(length(date))
df1 <- tibble(date, x)
df1

# A tibble: 366 × 2
date           x
<date>       <dbl>
  1  1910-01-01  1.72459562
2  1910-01-02  0.88216253
3  1910-01-03 -0.35434587
4  1910-01-04 -0.63401467
5  1910-01-05  0.18136909
6  1910-01-06 -0.09513488
7  1910-01-07 -1.03252313
8  1910-01-08  0.40924962
9  1910-01-09  0.90759866
10 1910-01-10  0.60456596
# ... with 356 more rows

Create the database

dbname = "test.sqlite3"
con <- dbConnect(SQLite(), dbname)

Add df1 to the database

dbWriteTable(con, "test", df1, append=TRUE)

let's see what has been created

dbListTables(con)
dbListFields(con, "test")

Connect to the database

test_db <- src_sqlite(path=dbname)

What happened to the date?

We lose the formatting which is problematic for subsequent processing.

tbl(test_db, "test") 

Source:   query [?? x 2]
Database: sqlite 3.11.1 [test.sqlite3]
date           x
<dbl>       <dbl>
  1  -21915 -0.05640646
2  -21914 -0.05640646
3  -21913 -0.05640646
4  -21912 -0.05640646
5  -21911 -0.05640646
6  -21910 -0.05640646
7  -21909 -0.05640646
8  -21908 -0.05640646
9  -21907 -0.05640646
10 -21906 -0.05640646

Can anyone recommend strategies for dealing with dates when using RSQLite?

Answer 1

There's no date or time datatype in SQLite. Two possible approaches are to save them as text or as an integer. I suspect they're getting stored as days before the unix epoch (1970-01-01) by default.

If you want to work with the dates as integers, you can select by date:

d <- as.numeric(as.Date("1910-01-04"))
filter(test, date < d)

Or convert back to a tbl with dates as dates:

collect(test) %>% mutate(date = as.Date(date, '1970-01-01'))

You could store the dates as text:

df1$ts <- as.character(df1$date)
dbWriteTable(con, "test", df1, overwrite=TRUE)
tbl(test_db, "test") 

This is subsettable in the way you'd hope:

`filter(test, date < "1910-01-04")`

You could also convert to a normal tbl and convert the text to dates:

collect(test) %>% mutate(date = as.Date(date))