Re: problem with date_trunc and jdbc

[Heikki Linnakangas <heikki(at)enterprisedb(dot)com>]

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