Что происходит с датой данных в базе данных при использовании RSQLite?

Я начал работать с 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?


r sqlite rsqlite
person boshek    schedule 11.01.2017    source источник
comment
Ознакомьтесь с этим вопросом и включенной ссылкой о датах SQLite (или их отсутствии): stackoverflow.com/questions/13462086/using-dates-with-rsqlite   -  person blongworth    schedule 11.01.2017
comment
Забавно - это очень помогает. В основном вам нужно уменьшить функциональность, чтобы добавить функциональность.   -  person boshek    schedule 12.01.2017
comment
SQLite не имеет типа даты, хотя у него есть функции даты, которые могут интерпретировать определенные числа или строки. Вот несколько примеров: github.com/ggrothendieck/sqldf База данных H2 может подойти лучше, если вы хотите выполнить такую ​​обработку, поскольку она имеет тип даты.   -  person G. Grothendieck    schedule 12.01.2017
comment
Ну, я думаю, что могу придерживаться sqlite просто потому, что мне нравится, как dplyr работает с ним. Эта ситуация на самом деле поднимает голову только из-за read_csv (здесь не используется), и когда он считывает данные, он автоматически определяет, должен ли столбец быть в формате даты. Хотя это легко исправить.   -  person boshek    schedule 12.01.2017

Ответы (1)


arrow_upward
5
arrow_downward

В SQLite нет типа данных даты или времени. Два возможных подхода — сохранить их как текст или как целое число. Я подозреваю, что по умолчанию они сохраняются за несколько дней до эпохи Unix (1970-01-01).

Если вы хотите работать с датами как целыми числами, вы можете выбрать по дате:

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

Или преобразовать обратно в таблицу с датами в качестве дат:

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")`

Вы также можете преобразовать в обычную таблицу и преобразовать текст в даты:

collect(test) %>% mutate(date = as.Date(date))
person blongworth    schedule 11.01.2017
comment
Вы можете использовать этот трюк и для даты и времени. Оба преобразования POSIXct -> числовое и -> POSIXct находятся в следующем: as.POSIXct(as.numeric(Sys.time()),origin="1970-01-01",tz="UTC") - person Dave X; 22.09.2017