在选择中创建分钟范围(15) [英] Create ranges of minutes (15) in select
问题描述
我正在创建一个选择，该选择使我超出表格结果的范围为15分钟的列.
I am creating a select which brings me beyond the results of the table a column with a range of 15 minutes.
我想做的是将00:00到00:15之间的小时数在0:15范围内分组. 在0:30范围内在0:16到0:30之间对寄存器进行分组之后.我将在一天中的所有时间里这样做.
What I am trying to do, is to group hours between 00:00 and 00:15 in a 0:15 range. After grouping the registers between 0:16 and 0:30 in a 0:30 range. This I will do for all hours in a day.
以下，我描述了我的选择.如果有人可以帮助我发展思路.非常感谢.
Below, I describe my select. If someone can help me develop a line of thought. Tks a lot.
CREATE OR REPLACE FORCE VIEW "ARADMIN"."GSC_VW_INC_DIARIOS"("ID_INCIDENTE", "STATUS_INCIDENTE", "DATAHORA_CRIACAO", "DATA_CRIACAO", "HORA_CRIACAO", "PRIORIDADE", "IMPACTO", "URGÊNCIA")
AS
SELECT T2318.C1,
CASE (T2318.C7)
WHEN 0
THEN 'NOVO'
WHEN 1
THEN 'DESIGNADO'
WHEN 2
THEN 'EM ANDAMENTO'
WHEN 3
THEN 'PENDENTE'
WHEN 4
THEN 'RESOLVIDO'
WHEN 5
THEN 'FECHADO'
WHEN 6
THEN 'CANCELADO'
END,
TO_CHAR(secs_to_datetime(T2318.C3),'DD/MM/YYYY HH24:MI:SS'),
TO_CHAR(secs_to_date(T2318.C3),'DD/MM/YYYY'),
CASE TO_CHAR(secs_to_hour(T2318.C3),'HH24:MI')
WHEN TO_CHAR(secs_to_hour(T2318.C3),'HH24:MI') BETWEEN ('00:00' AND '00:15')
THEN '00:15'
END,
CASE (T2318.C1000000164)
WHEN 0
THEN 'CRÍTICO'
WHEN 1
THEN 'ALTO'
WHEN 2
THEN 'MÉDIO'
WHEN 3
THEN 'BAIXO'
END,
CASE (T2318.C1000000163)
WHEN 1000
THEN 'EXTENSIVO/DIFUNDIDO'
WHEN 2000
THEN 'SIGNIFICATIVO/GRANDE'
WHEN 3000
THEN 'MODERADO/LIMITADO'
WHEN 4000
THEN 'MENOR/LOCALIZADO'
END,
CASE (T2318.C1000000162)
WHEN 1000
THEN 'CRÍTICO'
WHEN 2000
THEN 'ALTO'
WHEN 3000
THEN 'MÉDIO'
WHEN 4000
THEN 'BAIXO'
END
FROM T2318
WHERE T2318.C3 > 1434419999;
说明我在尝试什么.我将粘贴选择结果的打印内容
To ilustrate what I am trying. I will paste the print of select result
推荐答案
如果您以日期值开头，或者在这种情况下是转换为日期的值，则可以找到它属于操纵午夜后的秒数的日子；您可以使用SSSSS格式模型从to_char()获取.
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.
select to_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-18 18:25:49 66349
您可以将秒数舍入为15分钟的开始时间，方法是将其除以900(15 * 60)，将其舍入或舍入以获得整数值，然后再乘以900:
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:
select to_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-18 18:25:49 66349 73.7211111 73 65700
您可以通过将其添加回日期将其转换回时间:
And you can convert that back to a time by adding it back to a date:
select to_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-18 18:25:49 66349 65700 18:15:00
不过，您可能想保留日期，因此可以将其添加到trunc(<original_date>)中.我想除非您只有一天之内的数据，或者想显示捆绑在一起的多天内的同一时间.
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.
这里有一个演示，其中包含10次随机生成的时间，显示了他们被分配给的15分钟块:
Here's a demo with 10 randomly-generated times, showing the 15-minute block they're assigned to:
with t (date_field) as (
select sysdate - dbms_random.value(0, 1)
from dual
connect by level <= 10
)
select to_char(date_field, 'YYYY-MM-DD HH24:MI:SS') as datefield,
to_char(date_field, 'SSSSS') as time_secs,
floor(to_number(to_char(date_field, 'SSSSS'))/900) * 900
as fifteen_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') as fifteen_min_block
from t
order by datefield;
DATEFIELD TIME_ FIFTEEN_MIN_BLOCK_SECS FIFTEEN_MIN_BLOCK
------------------- ----- ---------------------- -------------------
2015-06-17 21:03:00 75780 75600 2015-06-17 21:00:00
2015-06-18 05:07:28 18448 18000 2015-06-18 05:00:00
2015-06-18 05:48:42 20922 20700 2015-06-18 05:45:00
2015-06-18 07:23:03 26583 26100 2015-06-18 07:15:00
2015-06-18 08:24:57 30297 29700 2015-06-18 08:15:00
2015-06-18 08:52:06 31926 31500 2015-06-18 08:45:00
2015-06-18 10:59:14 39554 38700 2015-06-18 10:45:00
2015-06-18 11:47:05 42425 42300 2015-06-18 11:45:00
2015-06-18 12:08:37 43717 43200 2015-06-18 12:00:00
2015-06-18 17:07:23 61643 61200 2015-06-18 17:00:00
所以您需要拥有
trunc(date_field)
+ (floor(to_number(to_char(date_field, 'SSSSS'))/900) * 900) / 86400
或更简单的
trunc(date_field)
+ floor(to_number(to_char(date_field, 'SSSSS'))/900) / 96
放在group by子句中，并且可能在选择列表中进行显示.
part in your group by clause, and probably in your select list for display.
假设T2318.C3是从纪元开始数秒，您可以直接对其进行操作，然后将其传递给您的secs_to_datetime函数:
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)
因此，与上述示例等效的演示(在CTE中具有十次随机生成的时间)将是:
So the equivalent demo to the one above, again with ten randomly-generated times in a CTE, would be:
with T2318(c3) as (
select 1434708000 - dbms_random.value(0, 80000) from dual
connect by level <= 10
)
select to_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/2015 12:34:02 1434630842 1434630600 18/06/2015 12:30:00
18/06/2015 15:06:25 1434639985 1434639600 18/06/2015 15:00:00
18/06/2015 16:43:27 1434645807 1434645000 18/06/2015 16:30:00
18/06/2015 18:57:25 1434653845 1434653100 18/06/2015 18:45:00
18/06/2015 19:01:09 1434654069 1434654000 18/06/2015 19:00:00
18/06/2015 20:54:09 1434660849 1434660300 18/06/2015 20:45:00
19/06/2015 03:59:48 1434686388 1434685500 19/06/2015 03:45:00
19/06/2015 06:58:09 1434697089 1434696300 19/06/2015 06:45:00
19/06/2015 07:36:36 1434699396 1434699000 19/06/2015 07:30:00
19/06/2015 07:47:26 1434700046 1434699900 19/06/2015 07:45:00
或者，如果以毫秒为单位，则除以900000.
Or if it's in milliseconds, divide and multiply by 900000.
这篇关于在选择中创建分钟范围(15)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
