Create Ranges Of Minutes (15) In Select

I am creating a select which brings me beyond the results of the table a column with a range of 15 minutes. What I am trying to do, is to group hours between 00:00 and 00:15 in a 0

Solution 1:

If you're starting with a date value, or in this case a value that has been converted to a date, you can find which 15 minute block of the day it belongs to be manipulating the number of seconds past midnight; which you can get from to_char() with the SSSSS format model.

selectto_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as now_time,
  to_char(sysdate, 'SSSSS') as now_secs
from dual;

NOW_TIME            NOW_S
------------------- -----
2015-06-1818:25:4966349

You can round the the number of seconds down to the start of a 15-minute period by dividing by 900 (15 * 60), truncating or flooring it to get an integer value, and multiply back by 900:

selectto_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as now_time,
  to_char(sysdate, 'SSSSS') as now_secs,
  to_number(to_char(sysdate, 'SSSSS'))/900 as calc1,
  floor(to_number(to_char(sysdate, 'SSSSS'))/900) as calc2,
  floor(to_number(to_char(sysdate, 'SSSSS'))/900) * 900 as calc3
from dual;

NOW_TIME            NOW_S      CALC1      CALC2      CALC3
------------------- ----- ---------- ---------- ----------
2015-06-1818:25:496634973.72111117365700

And you can convert that back to a time by adding it back to a date:

selectto_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as now_time,
  to_char(sysdate, 'SSSSS') as now_secs,
  floor(to_number(to_char(sysdate, 'SSSSS'))/900) * 900 as calc3,
  to_char(date '1970-01-01'
    + (floor(to_number(to_char(sysdate, 'SSSSS'))/900) * 900 / 86400),
    'HH24:MI:SS') as calc4
from dual;

NOW_TIME            NOW_S      CALC3 CALC4  
------------------- ----- ---------- --------
2015-06-1818:25:49663496570018:15:00

You probably want to preserve the date though, so you can add it to trunc(<original_date>) instead. Unless you only have data within a single day, or want to show the same time from multiple days bundled together, I suppose.

Here's a demo with 10 randomly-generated times, showing the 15-minute block they're assigned to:

witht(date_field)as(selectsysdate-dbms_random.value(0,1)fromdualconnectbylevel<=10)selectto_char(date_field,'YYYY-MM-DD HH24:MI:SS')asdatefield,to_char(date_field,'SSSSS')astime_secs,floor(to_number(to_char(date_field,'SSSSS'))/900)*900asfifteen_min_block_secs,to_char(trunc(date_field)+(floor(to_number(to_char(date_field,'SSSSS'))/900)*900)/86400,'YYYY-MM-DD HH24:MI:SS')asfifteen_min_blockfromtorderbydatefield;DATEFIELDTIME_FIFTEEN_MIN_BLOCK_SECSFIFTEEN_MIN_BLOCK-----------------------------------------------------------------2015-06-17 21:03:00 75780756002015-06-17 21:00:002015-06-18 05:07:28 18448180002015-06-18 05:00:002015-06-18 05:48:42 20922207002015-06-18 05:45:002015-06-18 07:23:03 26583261002015-06-18 07:15:002015-06-18 08:24:57 30297297002015-06-18 08:15:002015-06-18 08:52:06 31926315002015-06-18 08:45:002015-06-18 10:59:14 39554387002015-06-18 10:45:002015-06-18 11:47:05 42425423002015-06-18 11:45:002015-06-18 12:08:37 43717432002015-06-18 12:00:002015-06-18 17:07:23 61643612002015-06-18 17:00:00

So you'd need to have the

trunc(date_field)
  + (floor(to_number(to_char(date_field, 'SSSSS'))/900) * 900) / 86400

or the slightly simpler

trunc(date_field)
  + floor(to_number(to_char(date_field, 'SSSSS'))/900) / 96

part in your group by clause, and probably in your select list for display.

---

Assuming T2318.C3 is seconds since the epoch, you could manipulate that directly and then pass that to your secs_to_datetime function:

secs_to_datetime(floor(T2318.C3 / 900) * 900)

So the equivalent demo to the one above, again with ten randomly-generated times in a CTE, would be:

withT2318(c3) as (select1434708000 - dbms_random.value(0, 80000) from dual
  connect by level <= 10
)
selectto_char(secs_to_datetime(T2318.C3),'DD/MM/YYYY HH24:MI:SS') as datefield,
  T2318.C3 as time_secs,
  floor(T2318.C3/900) * 900 as fifteen_min_secs,
  to_char(secs_to_datetime(floor(T2318.C3 / 900) * 900),
    'DD/MM/YYYY HH24:MI:SS') as fifteen_min
from T2318
order by T2318.C3;

DATEFIELD              TIME_SECS FIFTEEN_MIN_SECS FIFTEEN_MIN       
------------------- ------------ ---------------- -------------------
18/06/201512:34:021434630842143463060018/06/201512:30:0018/06/201515:06:251434639985143463960018/06/201515:00:0018/06/201516:43:271434645807143464500018/06/201516:30:0018/06/201518:57:251434653845143465310018/06/201518:45:0018/06/201519:01:091434654069143465400018/06/201519:00:0018/06/201520:54:091434660849143466030018/06/201520:45:0019/06/201503:59:481434686388143468550019/06/201503:45:0019/06/201506:58:091434697089143469630019/06/201506:45:0019/06/201507:36:361434699396143469900019/06/201507:30:0019/06/201507:47:261434700046143469990019/06/201507:45:00

Or if it's in milliseconds, divide and multiply by 900000.

Solution 2:

In SQL when we talk about "grouping" we are most often talking about aggregating multiple rows together and summarizing the result. I get the impression that you just want to "round" time values down to the start of a 15-minute block. The math is pretty easy but you may still have a little work to get it to display he way you want it:

FLOOR(EXTRACT(MINUTEFROM datetime_column) /15) *15

or maybe:

'00:'||RIGHT('0'|| TO_CHAR(FLOOR(EXTRACT(MINUTEFROM datetime_column) /15) *15), 2)

I'm not sure if you truly have a datetime column to work with but it's not much different against a string value in a known format.

FLOOR(TO_NUMBER(RIGHT(HORA_CRIACAO, 2)) /15) *15

I wanted to use integer division but I'm not quite sure how that's accomplished in Oracle. Many people are surprised by that behavior though so it's possible that using FLOOR() is more clear anyway.