Group by Date with a Timestamp value?

[izreena]

 I have a report that I want to group by Date.

From Sql I retrived Timestamp field which is in the Oracle Date format.

However when I use group by Timestamp, my detail will display many records for one date.

For example, 1 January has record at 1pm, 2pm, 3pm. So the detail in the group will display 1 January 1pm, 1 January 2pm, 1 January 3pm.

 

My field F{TIMESTAMP} Field class is set to java.sql.Timestamp. But If I set it to java.util.Date, the text will appear as 1 January, but they dont get grouped together. The 1 January will be displayed 3 times!

 

My SQL query:

 

SELECT trunc(TIMESTAMP) as TIMESTAMP

FROM TABLE

WHERE TIMESTAMP BETWEEN $P{FromDate} AND $P{ToDate}

GROUP BY trunc(TIMESTAMP)

but this is causing loss of data. 

 

 

How do I group them, all the of them into just one group, 1 January?

Code:

 

Post Edited by themillie at 03/01/2012 05:54

[cbarlow3]

I'm not familiar with TRUNC, but assuming it returns a Date value, why not just return a date and a time, like this:

SELECT trunc(TIMESTAMP) as DATEFIELD, TIMESTAMP as TIMESTAMP

FROM TABLE

WHERE TIMESTAMP BETWEEN $P{FromDate} AND $P{ToDate}

ORDER BY TIMESTAMP

By the way, I took out the GROUP BY clause in this query, because you didn't have any aggregate functions like COUNT() or SUM() that necessitate it.  Now, if you want to use iReport Groups to add headers and or footers (with subotals, for example) per date, then you can now define your iReport group based on the new field $F{DATEFIELD}, but you still have access to the TIMESTAMP field to use in the Detail band.  I added ORDER BY TIMESTAMP, which accomplishes two things: first, it's intuitive that if you group by something in iReport, you normally want it to agree with the sort order...I could accomplish that by ordering off of TIMESTAMP or off of trunc(TIMESTAMP).  The second thing this accomplishes is that because I chose to order by TIMESTAMP instead of the date, it means that the details within each date will be in order by time as well, which is probably a good thing.

Carl