Automatically copy/update the last 7 days data (from current date) using R Studio.

[alikhan]

I am using R script to copy data from Carbon table to Postgres.
My requirement is to automatically copy/update the last 7 days data (from current date) from carbon table to Postgres.
Below is the script that I currently use to append data on daily basis but now I want to automate this to copy last 7 days data from carbon to Postgres.

query <- paste(" select * from new_table.table_name;" , sep="")
Result1 <- sqlQuery(ch,query)
dbWriteTable(postgres_conn, name = c("new_table","table_name"), value=Result1,append=TRUE,row.names=FALSE,overwrite=FALSE)

For last 7 days (from current date) I can update the query line like:
query <- paste(" select * from new_table.table_name where Date > 2022/04/13 ;" , sep="")

But I want to automate this process so that last 7 days data is automatically updated in Postgres.

Any help will be appreciated.

Thanks

[FJCC]

Does this give you what you want?

paste(" select * from new_table.table_name where Date > ", Sys.Date() - 7,";" , sep="")
[1] " select * from new_table.table_name where Date > 2022-04-14;"

[alikhan]

@FJCC I'll try this and update here accordingly.
Thanks.

[alikhan]

@FJCC I tried this but its not working.
One thing that I have noticed is that the Date column data-type is '"string" or is their any syntax mistake?

[FJCC]

You could try

query <- paste(" select * from new_table.table_name where Date > '", Sys.Date() - 7,"';" , sep="")

though I do not understand why your query

query <- paste(" select * from new_table.table_name where Date > 2022/04/13 ;" , sep="")

worked.