The problem is almost certainly due to the fact that tables in sqlite do not automatically get their date-formats set correctly.
usin the DBI package to write to a sqlite database works, but format does not appear to be set automatially.
filling the table with
library(DBI)
library(tidyverse)
library(plyr)
library(sqldf)
#-- !preview conn=DBI::dbConnect(RSQLite::SQLite(),"/cloud/project/testsqlitedb.sqlite")
conn <- DBI::dbConnect(RSQLite::SQLite(),"/cloud/project/testsqlitedb.sqlite")
os <- read_csv('os.csv')
dbListTables(conn)
dbWriteTable(conn, "os", os)
dbListTables(conn)
dbDisconnect(conn)
followed by this sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite(),"/cloud/project/testsqlitedb.sqlite")
select os.date,os,visits from OS
displays the date as numbers
date os visits
|17994| (not set) |17023|
|17994| Android |9373375|
|17994| BlackBerry |5433|