Same query, different results. Possible reasons?
For testing purposes, I am querying the same table from the same database using two different GUIs (RStudio and SquirreLSQL).
The query in the SquirreLSQL console looks like this:
select count(distinct idstr) from fact_table where date::date='2014-10-30' and (w>0 or x>0 or y>0)
And in RStudio I have the following code:
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,"databaseconnectionstring",user ="usr",password ="pwd",dbname = "db")
res <- dbSendQuery(con, "select count(distinct idstr) from fact_table where date::date='2014-10-30' and (w>0 or x>0 or y>0)")
A query executed in SquirreLSQL returns almost twice as many rows as in RStudio. What could cause the same exact query to return different values? The table and content are not changed.
+3
source to share
1 answer
Thanks to Yakub's answer, I realized that the GUIs are in different time zones. To fix this, I ran the following SQL line in SquirreLSQL to find the correct timezone:
SELECT current_setting('TIMEZONE')
It returned "America / New_York", so I ran the following line in R to get two programs in the same timezone:
dbGetQuery(con, "SET TIMEZONE TO 'America/New_York'")
0
source to share