r - 根据空值在 RSQLite 中切碎的日期时间值

Question

在使用 RSQLite 1.0.0 包从 SQLite 3 数据库中提取数据时，我遇到了一个奇怪的现象。除日期时间值的年份外，所有内容都被截断!

看一个例子:

下面我正在提取 DATETIME 列:

library(RSQLite)
src_path <- "../DataLocked/Study.db"
con <- dbConnect(SQLite(), src_path)
dbGetQuery(con, "SELECT Todesdatum FROM Kontraindikation LIMIT 10")

这让我得到以下结果:

        Todesdatum
   1          NA
   2          NA
   3        2004
   4          NA
   5        2006
   6          NA
   7          NA
   8          NA
   9          NA
   10         NA

现在我在同一张表中省略了 NON NULL 值:

dbGetQuery(con, "SELECT Todesdatum FROM Kontraindikation WHERE Todesdatum NOTNULL")

这让我得到以下结果:

            Todesdatum
1  2004-09-16 00:00:00
2  2006-04-20 00:00:00
3  2006-06-02 00:00:00
4  2007-09-15 00:00:00
5  2008-06-12 00:00:00
6  2005-10-04 00:00:00
7  2008-11-22 00:00:00
8  2005-12-22 00:00:00
9  2006-11-05 00:00:00
10 2006-02-08 00:00:00
...

现在我尝试在 DATETIME 字段上做一个字符串格式:

dbGetQuery(con, "SELECT strftime('%Y-%m-%d',Todesdatum) as fixed_Todesdatum FROM Kontraindikation 10")

有效:

        fixed_Todesdatum
   1          <NA>
   2          <NA>
   3       2004-09-16
   4          <NA>
   5       2006-04-20
   6          <NA>
   7          <NA>
   8          <NA>
   9          <NA>
   10         <NA>

我真的不知道发生了什么以及如何解决这个问题。如果有任何指点，我将不胜感激。

您好，亚历克斯

Answer 1

当前的 RSQLite 在数据类型检测方面有点弱。与大多数 DBMS 不同，SQLite 可以存储 arbitrary data types in each entry of a column .

您没有提交可重现的示例，但我能够重现问题:

library(tibble)
library(DBI)

a <- tibble(a = c(NA_integer_), b = 1)
b <- tibble(a = c("2014-06-04 00:00:00"), b = 2)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "test", a)
dbWriteTable(con, "test", b, append = TRUE)

dbReadTable(con, "test")

两个数据帧的a列有不同的数据类型，通过先写入a，该列被创建为INTEGER。如果我将 NA_integer_ 更改为 NA_character，问题就会消失。你能复制这个吗？

可能是您的 Todesdatum 列具有 INTEGER 或 REAL 亲和性，这会欺骗 RSQLite 相信第一个值是一个数字。更新列亲和性，或使用具有所需格式的 strftime 解决方法。

无论如何，我同意 RSQLite 应该在这方面做得更好。