在日期之间筛选Oracle SQL Developer
在日期之间筛选Oracle SQL Developer
提问于 2018-05-17 15:59:17
我运行这个查询是为了查看两个日期之间的数据，如下面的代码所示，但是我只获得当前日期的数据(如果日期是连续的)，或者如果有日期间隔，则在前一天获取数据。
我是说：
- 如果间隔(16/05/18 - 17/05/18)仅返回16/05/18的数据。
- 如果间隔为(16/05/18 - 18/05/18)，则只返回间隔(16/05/18 - 17/05/18)的数据。
代码：
SELECT
C.SESSIONID,
SUBSTR(C.ORIGINATINGNUMBER, INSTR(C.ORIGINATINGNUMBER, ':') + 1,
INSTR(C.ORIGINATINGNUMBER, '@') - INSTR(C.ORIGINATINGNUMBER, ':') - 1) AS Origen,
TO_CHAR(C.CALLTIMESTAMP, 'DD/MM/YYYY') AS Fecha,
TO_CHAR(C.CALLTIMESTAMP,'HH:MI') AS Hora,
C.DURATION AS Duracion_IVR,
(CASE C.ENDTYPE
WHEN 1
THEN 'IVR'
WHEN 2
THEN 'Transferida'
ELSE 'Colgada'
END) AS Estado,
A.SERVICIO,
A.OPT,
A.CONTRATO_ENVIADO,
A.RPTA_WS_C,
A.RPTA_WS_L,
A.DESCRIPTIVO
FROM
CDR C
JOIN
(SELECT DISTINCT(D.SESSIONID) AS ID,
NVL(
(SELECT B.MESSAGE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.ACTIVITYNAME = 'CAMP'
),' ') AS SERVICIO,
NVL(
(SELECT B.ACTIVITYNAME
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.MESSAGE = 'OPC_MENU'
),' ') AS OPT,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.VARNAME = 'CONT_ENV'
),' ') AS CONTRATO_ENVIADO,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.VARNAME = 'COD_RSLT_OPER'
AND B.ACTIVITYNAME = '000'
),' ') AS RPTA_WS_C,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.VARNAME = 'COD_RSLT_OPER'
AND B.ACTIVITYNAME = '001'
),' ') AS RPTA_WS_L,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.ACTIVITYNAME = 'MSG_RPTA'
),' ') AS DESCRIPTIVO
FROM VPAPPLOG D
) A
ON A.ID = C.SESSIONID
WHERE C.APPLICATIONNAME = 'IVR_AGBAR_Dllo'
AND C.CALLTIMESTAMP >= '16/05/18' AND C.CALLTIMESTAMP <= '17/05/18';回答 3
Stack Overflow用户
发布于 2018-05-17 16:27:06
您正在比较列值和字符串值，这意味着Oracle使用会话NLS设置隐式地将字符串转换为日期或时间戳。可以从执行计划的筛选步骤中看到这一点：
1 - filter("C"."CALLTIMESTAMP">=TO_TIMESTAMP('16/05/18') AND
"C"."CALLTIMESTAMP"<=TO_TIMESTAMP('17/05/18'))这些隐式转换值的时间组件设置为午夜。(它们也非常脆弱，因为它们依赖于您使用的匹配会话设置的字符串，它并不总是在您的控制之下)。
这意味着你正在寻找2018-05-16 :00:00和2018-05-17 00:00:00之间的值。这将在一天中的任何时间捕捉到价值，在16日，但只有在十七号的记录，正好在午夜。
通常的方法是，范围大于或等于你的开始日期，少于你的结束日期后的一天-这意味着你赶上每一个向上，但不包括当天午夜。
AND C.CALLTIMESTAMP >= timestamp '2018-05-16 00:00:00'
AND C.CALLTIMESTAMP < timestamp '2018-05-18 00:00:00'在十六号或十七号的任何时候都能找到所有的记录。
如果列实际上是日期而不是时间戳，则可以使用日期文本：
AND C.CALLTIMESTAMP >= date '2018-05-16'
AND C.CALLTIMESTAMP < date '2018-05-18';如果不想使用文本，可以使用to_date()或to_timestamp()，使用适当的显式格式掩码。我建议您无论如何使用完整的四位数年份，而不是2位数，这仍然会造成混淆(特别是在隐式转换方面，但也很容易明显出错.)
Stack Overflow用户
发布于 2018-05-17 16:02:08
我建议你在“16/05/18”和“17/05/18”之间使用C.CALLTIMESTAMP语句；
希望能帮到你。
Stack Overflow用户
发布于 2018-05-17 17:05:46
谢谢大家，我走了很简单的路
SELECT C.SESSIONID,
SUBSTR(C.ORIGINATINGNUMBER,INSTR(C.ORIGINATINGNUMBER, ':') + 1,INSTR(C.ORIGINATINGNUMBER, '@') - INSTR(C.ORIGINATINGNUMBER, ':') - 1) AS Origen,
TO_CHAR(C.CALLTIMESTAMP,'DD/MM/YYYY') AS Fecha,
TO_CHAR(C.CALLTIMESTAMP,'HH:MI') AS Hora,
C.DURATION AS Duracion_IVR,
(
CASE C.ENDTYPE
WHEN 1
THEN 'IVR'
WHEN 2
THEN 'Transferida'
ELSE 'Colgada'
END) AS Estado,
A.SERVICIO,
A.OPT,
A.CONTRATO_ENVIADO,
A.RPTA_WS_C,
A.RPTA_WS_L,
A.DESCRIPTIVO
FROM CDR C
JOIN
(SELECT DISTINCT(D.SESSIONID) AS ID,
NVL(
(SELECT B.MESSAGE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.ACTIVITYNAME = 'CAMP'
),' ') AS SERVICIO,
NVL(
(SELECT B.ACTIVITYNAME
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.MESSAGE = 'OPC_MENU'
),' ') AS OPT,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.VARNAME = 'CONT_ENV'
),' ') AS CONTRATO_ENVIADO,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.VARNAME = 'COD_RSLT_OPER'
AND B.ACTIVITYNAME = '000'
),' ') AS RPTA_WS_C,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.VARNAME = 'COD_RSLT_OPER'
AND B.ACTIVITYNAME = '001'
),' ') AS RPTA_WS_L,
NVL(
(SELECT B.VARVALUE
FROM VPAPPLOG B
WHERE D.SESSIONID = B.SESSIONID
AND B.ACTIVITYNAME = 'MSG_RPTA'
),' ') AS DESCRIPTIVO
FROM VPAPPLOG D
) A
ON A.ID = C.SESSIONID
WHERE C.APPLICATIONNAME = 'IVR_AGBAR_Dllo'
AND C.CALLTIMESTAMP BETWEEN '16/05/18' AND '17/05/18 11:59:59,000000000 PM';页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50395865
复制相关文章
相似问题
腾讯云开发者
