CODEWITHSUNDEEP
oracle-databasedate
Pooja
Pooja

Reputation: 333

Facing issue with date function

I am trying to get the value from dba_scheduler_job_run_details view. where log_date > (SYSDATE - 5/(24*60)) (i.e. data of last 5 mins)

But it is not providing me with the correct value.

For sample please find the data below: SQL Query:

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: image attachedenter image description here

Upvotes: 0

Views: 291

Answers (1)

San
San

Reputation: 4538

This is because log_date is timestamp with timezone datatype and when you are joining it with date datatype it will lead to two different times when Oracle does an implicit conversion to compare:

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

So if I execute you query from time zone with offset +5:30 on a server with offset -4:00, Oracle will convert the SYSDATE - 5/(24*60) to sysdate - (9:30 + 0:05).

Tell Oracle to convert log_date to date explicitly and then apply the filer of > (SYSDATE - 5/(24*60) as follows and you will be fine:

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;

Update: This query may have performance issue due to following reasons,

1) Oracle will convert datatype for each row for column LOG_DATE from timestamp with time zone to date.

2) The build in index on column LOG_DATE, if any, will not be used due to the CAST function.

Better solution is to convert the sysdate - 5/(24*60) to timestamp with time zone, you must be aware of the timezone of your Oracle server, assuming that the server is located in UTC -4:00, below query should work fine:

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;

Another easier approach as suggested by @mathguy, use INTERVAL function:

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;

Upvotes: 2

Related Questions