sql - 需要写一个存储过程从SQL配置单元中的yyyymmddhhmmss类型时间戳中提取小时数

Question

需要使用存储过程提取特定时间段内客户的来电号码。具有日期时间的列的格式为 yymmddhhmmss。

20181202112658
20181201102326

当它采用 DATE 格式时(11/09/2018 8:19:57)，我在下面使用它来执行上述操作并获取在特定时间内调用的电话数量。

选择 CALLING_NUMBER，

COUNT(CASE WHEN TO_CHAR(CALL_START_TIME,'HH24') BETWEEN '06' AND '08' THEN CALLING_NUMBER END) AS CDR6_9_COUNT,
SUM(CASE WHEN TO_CHAR(CALL_START_TIME,'HH24') BETWEEN '06' AND '08' THEN CALL_DURATION END) AS CDR6_9_DURATION,

Answer 1

只需使用substr():

SELECT CALLING_NUMBER,
       SUM(CASE WHEN SUBSTR(CALL_START_TIME, 9, 2) BETWEEN '06' and '08' THEN 1 ELSE 0 END) as CDR6_9_COUNT,
       . . .

如有必要，您可能需要先将值转换为字符串:

SELECT CALLING_NUMBER,
       SUM(CASE WHEN SUBSTR(CAST(CALL_START_TIME AS string), 9, 2) BETWEEN '06' and '08' THEN 1 ELSE 0 END) as CDR6_9_COUNT,
       . . .