search

poissonconsulting / readwritesqlite

An R package that enhances writing and reading data to and from SQLite databases
https://poissonconsulting.github.io/readwritesqlite/
Other
38 stars 1 forks source link

datetime columns that are stored as `TEXT` and contain 00:00:00 times causes all times to be dropped #39

Open evanamiesgalonski opened 1 year ago

evanamiesgalonski commented 1 year ago

not an issue when using column type TIMESTAMP in table definition

data <-
  tibble::tibble(
    date_time = as.POSIXct(c("1998-07-08 12:07:00", "1998-07-08 00:00:00"))
  )

data2 <-
  tibble::tibble(
    date_time = as.POSIXct(c("1998-07-08 12:07:00", "1998-07-08 00:00:01"))
  )

# readwritesqlite - 00:00:00 times present
conn <- readwritesqlite::rws_connect()

sql <- "CREATE TABLE data (date_time TEXT NOT NULL)"
DBI::dbExecute(conn, sql)

readwritesqlite::rws_write(data, x_name = "data", conn = conn)
readwritesqlite::rws_read_table("data", conn = conn)

# readwritesqlite - 00:00:00 times NOT present
conn <- readwritesqlite::rws_connect()

sql <- "CREATE TABLE data (date_time TEXT NOT NULL)"
DBI::dbExecute(conn, sql)

readwritesqlite::rws_write(data2, x_name = "data", conn = conn)
readwritesqlite::rws_read_table("data", conn = conn)