面对日期功能的问题

Question

我正在尝试从dba_scheduler_job_run_details视图中获取值。其中log_date >(SYSDATE-5/(24*60))(即最后5分钟的数据)

但它并没有为我提供正确的价值。

对于示例，请查找以下数据: SQL查询：

SELECT SYSDATE, SYSDATE - 5/(24*60),LOG_DATE,TRUNC(LOG_DATE),TO_CHAR(LOG_DATE,'YYYY/MM/DD HH:MI:SS')
FROM dba_scheduler_job_run_details 
WHERE LOG_DATE  > (SYSDATE - 5/(24*60))
ORDER BY LOG_DATE DESC;

o/p:图像附件

​

Answer 1

这是因为log_date是timestamp with timezone数据类型，当您使用日期数据类型加入它时，它将导致两次不同的情况，即Oracle进行隐式转换以进行比较：

SQL> SELECT SYSTIMESTAMP, CAST(SYSDATE AS TIMESTAMP WITH TIME ZONE) time_with_tz FROM dual;
SYSTIMESTAMP                                                                     TIME_WITH_TZ
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
19-MAR-18 09.57.07.654161 AM -04:00                                              19-MAR-18 09.57.07.000000 AM +05:30

因此，如果我使用偏移量为-4:00的服务器上的偏移量+5:30从时区执行查询，Oracle将将SYSDATE - 5/(24*60)转换为sysdate - (9:30 + 0:05)。

告诉甲骨文显式地将log_date转换为日期，然后按如下所示应用> (SYSDATE - 5/(24*60)的文件处理程序，您就可以了：

SELECT SYSDATE, SYSDATE - 5/(24*60),LOG_DATE,TRUNC(LOG_DATE),TO_CHAR(LOG_DATE,'YYYY/MM/DD HH:MI:SS')
FROM dba_scheduler_job_run_details 
WHERE cast(LOG_DATE AS DATE)  > (SYSDATE - 5/(24*60))
ORDER BY LOG_DATE DESC;

更新:由于以下原因，此查询可能存在性能问题，

1) Oracle将从带有时区的时间戳转换列LOG_DATE的每一行的数据类型。 2)由于CAST函数，列LOG_DATE上的构建索引(如果有的话)将不被使用。

更好的解决方案是将sysdate - 5/(24*60)转换为带有时区的时间戳，您必须知道Oracle服务器的时区，假设服务器位于UTC -4:00，下面的查询应该可以正常工作：

SELECT SYSDATE, SYSDATE - 5/(24*60),LOG_DATE,TRUNC(LOG_DATE),TO_CHAR(LOG_DATE,'YYYY/MM/DD HH:MI:SS')
FROM dba_scheduler_job_run_details 
WHERE LOG_DATE > TO_TIMESTAMP_TZ (to_char(SYSDATE - 5/(24*60), 'YYYY-MM-DD HH24:MI:SS')  || ' -4:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
ORDER BY LOG_DATE DESC;

另一种更简单的方法是使用区间函数，@mathguy提出了这样的方法：

SELECT SYSDATE, SYSDATE - 5/(24*60),LOG_DATE,TRUNC(LOG_DATE),TO_CHAR(LOG_DATE,'YYYY/MM/DD HH:MI:SS')
FROM dba_scheduler_job_run_details 
WHERE LOG_DATE > SYSTIMESTAMP - INTERVAL '5' MINUTE
ORDER BY LOG_DATE DESC;