从日期开始减去1年不加润滑剂 [英] Subtracting 1 Year from Date without Lubridate

问题描述

我在数据库中有一个数据框，其日期尺寸是我使用RPostgres拉出的.每个日期"的格式为"YYYY-MM-DD".我想添加一个比初始日期早一年的日期列(标为"lookback_date").

I have a dataframe in a database with a date dimension that I pulled using RPostgres. Each 'date' is in the format 'YYYY-MM-DD. I'd like to add a new column of dates (labeled 'lookback_date') that are one year earlier than the initial dates.

要清楚，如果观察的日期"为"2000-01-01"，我想为该观察添加新的"lookback_date"为"1999-01-01".不幸的是，我不知道该怎么做.通常，我会使用Lubridate，但据我所知，它不适用于dbplyr.到目前为止，这是我的代码的简化版本.直到mutate函数，我实际代码中的所有内容都可以正常工作.

To be clear, if an observation's 'date' were '2000-01-01', I'd want to add a new 'lookback_date' of '1999-01-01' to that observation. Unfortunately, I can't figure out how to do that. Normally, I'd use Lubridate, but, from what I can tell, it doesn't work with dbplyr. Here's a streamlined version of my code so far. Everything in my actual code works fine up until the mutate function.

# Packages
library(dbplyr)
library(RPostgres)

# Connect to db 
drv <- dbDriver("Postgres")

# Setup connect to db
conn <- dbConnect(drv,
                  dbname = etc,)

# Define table to use in db
table <- tbl(conn, in_schema("xyz", "abc"))

#Select columns and filter
base_data <- table %>%
  #Filter for pertinent data
  filter(date > as.Date("2018-01-01") & date <= as.Date("2020-01-01"))

modified_data <- base_data %>%
mutate(lookback_date = date - 365)

还有另一种创建日期的新列的方法吗?

Is there another way I can create this new column of dates?

谢谢！

推荐答案

您正确的认为lubridate和dbplyr不能很好地配合使用(现在).因此，我使用sql片段进行大多数dbplyr日期操作.

You are correct that lubridate and dbplyr to not play nicely together (right now). As a result of this, I do most of my dbplyr date manipulation using fragments of sql.

基于此答案和

Based on this answer and this site, the postgresql syntax to add/subtract time from a date is:

SELECT old_date + INTERVAL '1 day' AS new_date;

基于此，我将尝试以下操作:

Based on this I would try the following:

output = base_data %>% mutate(lookback_date = date - sql("INTERVAL '1 year'"))

当我使用模拟连接执行此操作时，它会产生正确的语法:

When I do this with a simulated connection, it produces the correct syntax:

library(dplyr)
library(dbplyr)

df = data.frame(my_num = c(1,2,3), my_dates = as.Date(c('2000-01-01','2000-02-02','2000-03-03')))
df = tbl_lazy(df, con = simulate_postgres())

output = df %>% mutate(new_date = my_dates - sql("INTERVAL '1 year'"))

show_query(output)
# <SQL>
# SELECT `my_num`, `my_dates`, `my_dates` - INTERVAL '1 year' AS `new_date`
# FROM `df`

---

更新:在评论中，您首先要从日期时间转换为日期.

---

UPDATE: From comment, you first want to convert from date-time to date.

看来dbplyr确实支持将 as.Date 转换为PostgreSQL( as.Date 是base R的一部分，而不是lubridate的一部分).因此，您可以使用以下命令将列转换(转换)为日期:

It appears that dbplyr does support the translation of as.Date to PostgreSQL (as.Date is part of base R, not part of lubridate). Hence you can use the following to cast (convert) a column to date:

library(dplyr)
library(dbplyr)

df = data.frame(my_str = c('2000-01-01','2000-02-02','2000-03-03'))
df = tbl_lazy(df, con = simulate_postgres())

output = df %>% mutate(my_date = as.Date(my_str))

show_query(output)
# <SQL>
# SELECT `my_str`, CAST(`my_str` AS DATE) AS `my_date`
# FROM `df`

似乎PostgreSQL也不允许您添加一年的间隔.一种替代方法是从日期中提取年，月和日，在年中添加一个，然后重新组合.

It also appears that PostgreSQL does not allow you to add an interval of one year. One alternative to this is to extract the year, month, and day from the date, add one to the year and then recombine.

遵循这两个引用( postgre日期引用和 date_part功能)和

Following these two references (postgre date references and date_part fuction) and this answer, you probablywant something like the following:

output = df %>%
  mutate(the_year = DATE_PART('year', my_date),
         the_month = DATE_PART('month', my_date),
         the_day = DATE_PART('day', my_date)) %>%
  mutate(new_date = MAKE_DATE(the_year + 1, the_month, the_day)

这篇关于从日期开始减去1年不加润滑剂的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！