4

I would like to group a search result by days, but unfortunately, the definition is not a day from midnight to midnight (00:00-24:00), but from 06:00 to 06:00.

Any easy solution? If possible in PL-SQL

rdmueller
  • 10,742
  • 10
  • 69
  • 126

1 Answers1

8

It should be as simple as this:

GROUP BY TRUNC(DATE - 6/24)

- 6/24 subtracts 6 hours from the datetime in the column DATE and thus all times between 06:00 and 06:00 will be the same day. TRUNC then removes the time part as you only need the date.

Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
  • +1. Some solution like the above in which you substruct 6 hours from date – niktrs Jun 28 '11 at 09:11
  • hm. ok, needed to figure out how to make the solution work. This works for me: `SELECT trunc(DATE-6/24), ID FROM TABLE GROUP BY trunc(DATE-6/24), ID` – rdmueller Jun 28 '11 at 10:03