How to "group" by DATETIME range?

Question

I am trying to strengthen a transaction report based on a datetime range for a business that can be opened in two days, depending on shift management.

The user can select the datetime range (monthly, daily, weekly, free ...), the query that I implemented receives startDateTime and EndDateTime and returns all transactions grouped by day.

those.

DateTime       Total Sales
---------------------------
10/15/2010      $2,300.38
10/16/2010      $1,780.00
10/17/2010      $4,200.22
10/20/2010      $900.66

My problem is that if the business shift is established, for example, from 05.00 to 02.00 the next day, all transactions made from midnight to 02:00 will be grouped the next day ... and so on ... the totals damaged. When a business has such a shift, it needs a report based on this shift, but without correcting the code (I use Java that calls Oracle's own queries), I cannot get the requested report.

I am wondering if there is some clever way to group these sets of transactions by datetime range using none other than Oracle.

Here is the query for the month of July:

SELECT Q1.dateFormat, NVL(Q1.sales, 0) 
    FROM (
        SELECT to_date(to_char(tx.datetimeGMT +1/24 , 'mm-dd-yyyy'), 'mm-dd-yyyy') AS dateFormat                    
                , NVL(SUM(tx.amount),0) AS sales
            FROM Transaction tx
            WHERE tx.datetimeGMT > to_date('20100801 08:59:59', 'yyyymmdd hh24:mi:ss') +1/24  
                AND tx.datetimeGMT < to_date('20100901 09:00:00', 'yyyymmdd hh24:mi:ss') + 1/24  
            GROUP BY to_date(to_char(tx.datetimeGMT +1/24 , 'mm-dd-yyyy'), 'mm-dd-yyyy') 
    ) Q1 
    ORDER BY 1 DESC

Answer 1

, , , :

SELECT CASE 
     WHEN EXTRACT(HOUR FROM TX.DATETIME) >= 5 THEN TO_CHAR(TX.DATETIME,'DD-MM-YYYY')
     WHEN EXTRACT(HOUR FROM TX.DATETIME) BETWEEN 0 AND 2 THEN TO_CHAR(TX.DATETIME-1,'DD-MM-YYYY')
     WHEN EXTRACT(hour from tx.datetime) between 2 and 5 THEN to_char(TX.DATETIME-1,'DD-MM-YYYY')
   END AS age, 
   NVL(SUM(tx.amount),0) AS sales
FROM TRANSACTION TX
WHERE tx.datetime > to_date('20100801 08:59:59', 'yyyymmdd hh24:mi:ss') 
  AND TX.DATETIME < TO_DATE('20100901 09:00:00', 'yyyymmdd hh24:mi:ss')
GROUP BY CASE 
     WHEN EXTRACT(HOUR FROM TX.DATETIME) >= 5 THEN TO_CHAR(TX.DATETIME,'DD-MM-YYYY')
     WHEN EXTRACT(HOUR FROM TX.DATETIME) BETWEEN 0 AND 2 THEN TO_CHAR(TX.DATETIME-1,'DD-MM-YYYY')
     WHEN EXTRACT(hour from tx.datetime) between 2 and 5 THEN to_char(TX.DATETIME-1,'DD-MM-YYYY')
   END 
ORDER BY 1

Answer 2

, . , VARCHAR.

Answer 3

If the first shift of the day starts at 08:00, and the last shift of the same day ends at 07:59 the next day, you can use something like this to group transactions by switch date.

select trunc(trans_date - interval '8' hour) as shift_date
      ,sum(amount)
  from transactions
 group 
    by trunc(trans_date - interval '8' hour)
 order 
    by shift_date desc;

Answer 4

You can try this approach (just from the head, not even sure if it works):

select
trans_date,
trans_shift,
aggregates(whatever)
from (
    select 
    -- we want to group by normalized transaction date, 
    -- not by real transaction date
    normalized_trans_date,
    -- get the shift to group by
    case 
      when trans_date between trunc(normalized_trans_date) + shift_1_start_offset and 
                              trunc(normalized_trans_date) + shift_1_end_offset then
        1
      when trans_date between trunc(normalized_trans_date) + shift_2_start_offset and 
                              trunc(normalized_trans_date) + shift_2_end_offset then
        2
      ...
      when trans_date between trunc(normalized_trans_date) + shift_N_start_offset and 
                              trunc(normalized_trans_date) + shift_N_end_offset then
        N
    end trans_shift,
    whatever
    from (
        select
        -- get a normalized transaction date: if date is before 1st shift
        -- it belongs to the day before
        case 
          when trans_date - trunc(trans_date) < shift_1_start_offset then
            trans_date - 1
          else
            trans_date
        end normalized_trans_date,
        t.*
        from
        transactions t
    )
)
group by trans_date, trans_shift