I am having an issue in using the RPostgres package to connect with Redshift. I am unsure if it is an issue with our database set up, or if it is a known issue with the package.
I am getting different results when I use the same exact query in Redshift vs. using it in R with the RPostgres package.
It appears entirely due to the date math, as my overall row counts match, as well as everything else, when not using dates.
As an example, this may be a query I run in Redshift (using Metabase). If I run the same exact query in R with the RPostgres package, I will get completely different results.
SELECT
orders.*
FROM
orders
WHERE
orders.date >= current_date-3
AND
orders.date < current_date-2
The dates in Metabase make sense. It shows only one day, three days ago. However, in R, it shows 2 days.
For the purposes of this example, consider date a timestamp.
Has anyone ran into this, or know of an existing issue and work around?
It's usually better to be absolutely explicit with dates to make sure that session settings are not unintentionally affecting the query. Try this:
SELECT
orders.*
FROM
orders
WHERE
orders.date >= date_trunc('day', current_timestamp at time zone 'utc') - '3 days'::interval
AND
orders.date < date_trunc('day', current_timestamp at time zone 'utc') - '2 days'::interval
You may have to change the time zone from utc if your dates are implicitly stored in a different time zone.