在select中创建分钟范围（15）

I am creating a select which brings me beyond the results of the table a column with a range of 15 minutes.

我正在创建一个选择,它使我超出了表格的结果,范围为15分钟。

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.

我想要做的是在0:15范围内将时间从00:00到00:15分组。在0:30范围内将寄存器在0:16和0:30之间分组。我将在一天中的所有时间做这件事。

Below, I describe my select. If someone can help me develop a line of thought. Tks a lot.

下面,我描述我的选择。如果有人可以帮助我发展思路。 Tks很多。

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

说明我正在尝试的东西。我将粘贴选择结果的打印

2 个解决方案

#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.

如果您从日期值开始,或者在这种情况下是已转换为日期的值,您可以找到它所属的当天的15分钟块操纵午夜的秒数;您可以使用SSSSS格式模型从to_char()获取。

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

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:

您可以将秒数向下舍入到15分钟的开始时间除以900(15 * 60),截断或将其放置以获得整数值,然后再乘以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

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.

您可能希望保留日期,因此您可以将其添加到trunc( )。我想,除非您在一天内只有数据,或者希望在捆绑在一起的多天显示相同的时间。

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

这是一个包含10个随机生成时间的演示,显示了他们被分配到的15分钟块:

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

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.

您可以在您的group by子句中使用,也可以在您的选择列表中进行显示。

---

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

假设T2318.C3是自纪元以来的秒数,您可以直接操作它,然后将其传递给您的secs_to_datetime函数:

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:

所以上面的一个等效演示,再次在CTE中有十个随机生成的时间,将是:

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

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

或者,如果它是以毫秒为单位,则除以并乘以900000。

#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:

在SQL中,当我们谈论“分组”时,我们经常讨论将多行聚合在一起并总结结果。我得到的印象是你只想将时间值“舍入”到15分钟开始。数学很简单,但你可能还有一点工作要按照你想要的方式显示它:

FLOOR(EXTRACT(MINUTE FROM datetime_column) / 15) * 15

or maybe:

'00:' || RIGHT('0' || TO_CHAR(FLOOR(EXTRACT(MINUTE FROM 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.

我想使用整数除法,但我不太确定在Oracle中如何实现。许多人对这种行为感到惊讶,但是无论如何使用FLOOR()都是可能的。