在RSQLite中使用日期

Question

如何使用RSQLite编写带日期的SQL查询。下面是一个例子。dbGetQuery不返回任何行。

require(RSQLite)
require(ggplot2)
data(presidential)
m <- dbDriver("SQLite")
tmpfile <- tempfile('presidential', fileext='.db')
conn <- dbConnect(m, dbname=tmpfile)
dbWriteTable(conn, "presidential", presidential)
dbGetQuery(conn, "SELECT * FROM presidential WHERE Date(start) >= Date('1980-01-01')")

Answer 1

为了说明这一点，它工作得很好：

tmpfile <- tempfile('presidential', fileext='.db')
conn <- dbConnect(m, dbname=tmpfile)
p <- presidential
p$start <- as.character(p$start)
p$end <- as.character(p$end)

dbWriteTable(conn, "presidential", p)
dbGetQuery(conn, "SELECT * FROM presidential WHERE start >= '1980-01-01'")

您可以在文档here中了解到SQLite中缺少原生日期类型。我在SQLite中使用字符串作为日期已经很长时间了，实际上我已经完全忘记了这个问题。

是的，我已经编写了一个小的R函数，它可以将数据框中的任何日期列转换为字符。对于简单的比较，将它们保存在YYYY-MM-DD中就足够了，如果我需要进行算术运算，我会在R中转换它们。

Answer 2

根据@joran的回答，下面是一个简单的函数，用于将日期列转换为data.frame的字符串。

mutate(df, across(where(is.Date), ~ format(.x, "%Y.%m.%d")))

Answer 3

我发现使用RSQLite和dplyr是使用R和SQLite保持类型一致的最方便的方法。特别是，extendend_types = TRUE确保DATE、DATETIME / TIMESTAMP和TIME类型的列被映射到相应的R类(至少在RSQLite的2.2.8版之后)。

library(dplyr)
library(RSQLite)
library(ggplot2)

data(presidential)

mydb <- dbConnect(SQLite(), "presidential.sqlite", extended_types = TRUE)
dbWriteTable(mydb, "presidential", presidential)

tbl(mydb, "presidential") %>% 
  filter(start >= as.Date("1980-01-01")) %>% 
  collect()

您还可以将后一个集合格式化为get查询：

dbGetQuery(mydb, "SELECT * FROM presidential WHERE start >= CAST('1980-01-01' AS DATE)")