problem with date_trunc and jdbc

From: Marcos Truchado <mtruchado(at)ya(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: problem with date_trunc and jdbc
Date: 2007-07-24 23:19:58
Message-ID: 46A6891E.7090809@ya.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Heikki Linnakangas 2007-07-25 09:54:43 Re: problem with date_trunc and jdbc
Previous Message Marek Lewczuk 2007-07-24 11:27:33 Re: Raise Notice & Java