r - 使用 RSQLite 时，数据库中的日期数据会发生什么？

Question

我已经开始使用 RSQLite 和 dplyr 来有效地处理大型数据集。但是，我一直无法协调如何让 RSQLite 格式化日期或这里有哪些最佳实践。下面的例子应该说明这个过程对我来说在哪里失败:

library(tidyverse)
library(RSQLite)

数据

日期格式正确

date=seq(as.Date("1910/1/1"), as.Date("1911/1/1"), "days")
x=rnorm(length(date))
df1 <- tibble(date, x)
df1

# A tibble: 366 × 2
date           x
<date>       <dbl>
  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

创建数据库

dbname = "test.sqlite3"
con <- dbConnect(SQLite(), dbname)

将 df1 添加到数据库

dbWriteTable(con, "test", df1, append=TRUE)

让我们看看已经创建了什么

dbListTables(con)
dbListFields(con, "test")

连接到数据库

test_db <- src_sqlite(path=dbname)

约会发生了什么？

我们丢失了对后续处理有问题的格式。

tbl(test_db, "test") 

Source:   query [?? x 2]
Database: sqlite 3.11.1 [test.sqlite3]
date           x
<dbl>       <dbl>
  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

任何人都可以推荐使用 RSQLite 时处理日期的策略吗？

Answer 1

SQLite 中没有日期或时间数据类型。两种可能的方法是将它们保存为文本或整数。我怀疑默认情况下它们会存储在 unix 纪元 (1970-01-01) 之前的几天。

如果要将日期作为整数处理，可以按日期选择:

d <- as.numeric(as.Date("1910-01-04"))
filter(test, date < d)

或者转换回以日期为日期的 tbl:

collect(test) %>% mutate(date = as.Date(date, '1970-01-01'))

您可以将日期存储为文本:

df1$ts <- as.character(df1$date)
dbWriteTable(con, "test", df1, overwrite=TRUE)
tbl(test_db, "test") 

这是您希望的子集化方式:

`filter(test, date < "1910-01-04")`

您还可以转换为普通 tbl 并将文本转换为日期:

collect(test) %>% mutate(date = as.Date(date))