CODEWITHSUNDEEP
sqldatabaseoracle-databaserdbms
user2316458
user2316458

Reputation: 31

SQL - Grouping results by custom 24 hour period

I need to create an Oracle 11g SQL report showing daily productivity: how many units were shipped during a 24 hour period. Each period starts at 6am and finishes at 5:59am the next day.

How could I group the results in such a way as to display this 24 hour period? I've tried grouping by day, but, a day is 00:00 - 23:59 and so the results are inaccurate.

The results will cover the past 2 months.

Many thanks.

Upvotes: 3

Views: 782

Answers (4)

Ben
Ben

Reputation: 1927

Assuming that you have a units column or similar on your table, perhaps something like this:

SQL Fiddle 

SELECT 
  TRUNC(us.shipping_datetime - 0.25) + 0.25 period_start
, TRUNC(us.shipping_datetime - 0.25) + 1 + (1/24 * 5) + (1/24/60 * 59) period_end
, SUM(us.units) units
FROM units_shipped us
GROUP BY TRUNC(us.shipping_datetime - 0.25)
ORDER BY 1

This simply subtracts 6 hours (0.25 of a day) from each date. If the time is earlier than 6am, the subtraction will make it fall prior to midnight, and when the resultant value is truncated (time element is removed, the date at midnight is returned), it falls within the grouping for the previous day.

Results:

|                 PERIOD_START |                   PERIOD_END | UNITS |
-----------------------------------------------------------------------
| April, 22 2013 06:00:00+0000 | April, 23 2013 05:59:00+0000 |     1 |
| April, 23 2013 06:00:00+0000 | April, 24 2013 05:59:00+0000 |     3 |
| April, 24 2013 06:00:00+0000 | April, 25 2013 05:59:00+0000 |     1 |

The bit of dynamic maths in the SELECT is just to help readability of the results. If you don't have a units column to SUM() up, i.e. each row represents a single unit, then substitute COUNT(*) instead.

Upvotes: 0

Paddy Carroll
Paddy Carroll

Reputation: 538

Days are whole numbers in oracle so 6 am will be 0.25 of a day so :

select 
trunc(date + 0.25) as period, count(*) as number
from table
group by trunc(date + 0.25 )

I havent got an oracle to try it on at the moment.

Upvotes: 2

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

group by trunc(your_date - 1/4)

Upvotes: 2

Menelaos
Menelaos

Reputation: 25725

Well, you could group by a calculated date. So, add 6 hours to the dates and group by that which would then technically group your dates correctly and produce the correct results.

Upvotes: 0

Related Questions