title: What happens to date data in a database when using RSQLite?
body: <p>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:</p>
  
  <pre><code>library(tidyverse)
  library(RSQLite)
  </code></pre>
  
  <h2>Data</h2>
  
  <p>Date is formatted appropriately</p>
  
  <pre><code>date=seq(as.Date("1910/1/1"), as.Date("1911/1/1"), "days")
  x=rnorm(length(date))
  df1 &lt;- tibble(date, x)
  df1
  
  # A tibble: 366 × 2
  date           x
  &lt;date&gt;       &lt;dbl&gt;
    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
  </code></pre>
  
  <h2>Create the database</h2>
  
  <pre><code>dbname = "test.sqlite3"
  con &lt;- dbConnect(SQLite(), dbname)
  </code></pre>
  
  <h2>Add df1 to the database</h2>
  
  <pre><code>dbWriteTable(con, "test", df1, append=TRUE)
  </code></pre>
  
  <h1>let's see what has been created</h1>
  
  <pre><code>dbListTables(con)
  dbListFields(con, "test")
  </code></pre>
  
  <h2>Connect to the database</h2>
  
  <pre><code>test_db &lt;- src_sqlite(path=dbname)
  </code></pre>
  
  <h2>What happened to the date?</h2>
  
  <p>We lose the formatting which is problematic for subsequent processing.</p>
  
  <pre><code>tbl(test_db, "test") 
  
  Source:   query [?? x 2]
  Database: sqlite 3.11.1 [test.sqlite3]
  date           x
  &lt;dbl&gt;       &lt;dbl&gt;
    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
  </code></pre>
  
  <p>Can anyone recommend strategies for dealing with dates when using RSQLite?</p>