The same sql statement works differently in Jasper and pgAdmin

Question

I face a problem with SQL query in JasperSoft studio. I am trying to get records from posgreSQL database from between two dates. I use this statement:

SELECT spent_on FROM time_entries WHERE spent_on BETWEEN date '2014-01-01' AND date '2014-11-1' ORDER BY spent_on DESC

or simpler but with the same problem:

SELECT spent_on FROM time_entries WHERE spent_on BETWEEN '2014-01-01' AND '2014-11-1' 

also using < and > ends the same way.

spent_on column is defined as: spent_on date NOT NULL.

When I run this query in pgAdmin I get all rows I expected to receive, however when I try the same statement in JasperSoft Studio I get none.

I found out that I can get some rows when I change range to: "between date '2012-01-01' and date '2014-11-1'" and the last entry I get is from 2012-05-29. (where in database last one is from 2014-11-12)

Database store data for Redmine.

SOLVED

Reason: different schema used in Jaspersoft Studio and pgadmin.

Answer 1

Try without the date keyword. Jasper may be understanding this the wrong way. (Just a shot in the dark.) String literals are cast to date automatically in this context:

SELECT spent_on
FROM   time_entries
WHERE  spent_on BETWEEN '2014-01-01' AND '2014-11-1'
ORDER  BY spent_on DESC;

I would also have suggested ORDER BY spent_on DESC NULLS LAST, but you say the column is defined NOT NULL, so not applicable. Details.

Also, the observed behavior (not getting the latest rows) would suggest you are not connected to the same database or running the query with a different search_path, so you end up with a different table of the same name in a different schema. Details:

• How does the search_path influence identifier resolution and the "current schema"

Answer 2

Answer by @GregG

The different schemas were used in Jaspersoft Studio and pgadmin