Thread: problem with date_trunc and jdbc

Hello people, I have an strange problem here.
Suppose I have a table mytable with a column mydate of type date.

if I launch this SQL directly with psql:

SELECT date_trunc('month', mydate)::date FROM mytable WHERE
mytable.mydate BETWEEN '2000-07-25 +02:00' AND '2004-07-25 +02:00' GROUP
BY date_trunc('month', mydate);

I obtaint the months/years in where exists any record in this table with
a date stored in mydate that uses this month/year, all between 2 dates

This sql works ok under psql. But if I try to execute it with jdbc:

String sql = "SELECT date_trunc('month', mydate)::date FROM mytable
WHERE mytable.mydate BETWEEN ? AND ? GROUP BY date_trunc('month', mydate)";

PreparedStatement pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

Calendar calendar = Calendar.getInstance();

//initial date, suppose f_ini has the correct date
calendar.setTime(f_ini);
pstmt.setDate(1, new java.sql.Date(calendar.getTimeInMillis()));

//end date, suppose f_end has the correct date
calendar.setTime(f_end);
pstmt.setDate(2, new java.sql.Date(calendar.getTimeInMillis()));

pstmt.executeQuery();

The code works ok, but when it reaches to executeQuery() statement it
fails with message "mytable.mydate must be used in group by clause or in
aggregate function"

Where is the fault?.

I'm using postgresql 8.1.9 and jdbc driver 8.1 build 409 JDBC3

Best regards

Marcos Truchado wrote:
> Suppose I have a table mytable with a column mydate of type date.
>
> if I launch this SQL directly with psql:
>
> SELECT date_trunc('month', mydate)::date FROM mytable WHERE
> mytable.mydate BETWEEN '2000-07-25 +02:00' AND '2004-07-25 +02:00' GROUP
> BY date_trunc('month', mydate);
>
> I obtaint the months/years in where exists any record in this table with
> a date stored in mydate that uses this month/year, all between 2 dates
>
> This sql works ok under psql. But if I try to execute it with jdbc:
>
> String sql = "SELECT date_trunc('month', mydate)::date FROM mytable
> WHERE mytable.mydate BETWEEN ? AND ? GROUP BY date_trunc('month', mydate)";
>
> PreparedStatement pstmt = conn.prepareStatement(sql,
> ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
>
> Calendar calendar = Calendar.getInstance();
>
> //initial date, suppose f_ini has the correct date
> calendar.setTime(f_ini);
> pstmt.setDate(1, new java.sql.Date(calendar.getTimeInMillis()));
>
> //end date, suppose f_end has the correct date
> calendar.setTime(f_end);
> pstmt.setDate(2, new java.sql.Date(calendar.getTimeInMillis()));
>
> pstmt.executeQuery();
>
> The code works ok, but when it reaches to executeQuery() statement it
> fails with message "mytable.mydate must be used in group by clause or in
> aggregate function"

What exactly is that the error message you're getting? I couldn't find
that phrase anywhere in the PostgreSQL or the JDBC driver source code.

> I'm using postgresql 8.1.9 and jdbc driver 8.1 build 409 JDBC3

I tried to reproduce this but it works fine for me. Can you construct a
complete self-contained test program?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com