Same query, different results. Possible causes?

Question

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

The query done in SquirreLSQL returns almost twice as many rows as the one done in RStudio. What could cause the same exact query to return different values? The table and contents do not change.

I think it has something to do with the date, because i tested it with just the variables, and it worked — zebrainatree, Nov 06 '14 at 20:54
Have you tried to query the database for the current time? It seems the only place it can differ is the timezone. — Jakub Kania, Nov 06 '14 at 20:57

score 0 · Accepted Answer · answered Nov 06 '14 at 21:46

Thanks to Jakub's response, I realized that the GUIs were in different timezones. To fix this, I ran the following line of SQL in SquirreLSQL to find the correct timezone:

SELECT  current_setting('TIMEZONE')

It returned "America/New_York", so I then ran the following line in R to get the two programs in the same timezone:

dbGetQuery(con, "SET TIMEZONE TO 'America/New_York'")

Same query, different results. Possible causes?

1 Answers1