In this article we will look into finding out changes from the database in a specific timeframe including specific time.
We all know that the LAST_UPDATE_DATE stores the datetime in UTC/GMT timezone. And if we want to fetch the data from yesterday, we can pull data based on the below condition.
trunc(LAST_UPDATE_DATE) = trunc(sysdate-1)
This will fetch all transactions that occurred yesterday. However this fetches the transactions occurred with respect to UTC timezone.
Most of the clients stay in a different timezone like EST, CST, PST etc. so the above query returns incorrect results as there will a difference between these timezones and UTC timezone. First we need to convert the LAST_UPDATE_DATE to the required timezone and then compare it against sysdate.
trunc(NEW_TIME(LAST_UPDATE_DATE,'GMT','EST')) = trunc(NEW_TIME(SYSDATE-1,'GMT','EST'))
This query will fetch all the transactions that happened in the EST timezone.
Now if you want to query for changes in a specified time like 2PM to 5PM then you will have to further fine tune it to get to that specific timeframe.
to_char(NEW_TIME(LAST_UPDATE_DATE,'GMT','EST'),'YYYY-MM-DD HH24:MI:SS') between to_char(NEW_TIME(SYSDATE-1,'GMT','EST'),'YYYY-MM-DD') || ' 12:00:00' and to_char(NEW_TIME(SYSDATE-1,'GMT','EST'),'YYYY-MM-DD') || ' 17:00:00'
The above query is concatenating the time to the date in EST and then comparing against the EST converted LAST_UPDATE_DATE.
Other Timezone specific functions that can be used in SQL Query:
to_char(NEW_TIME(SR_REOPEN_DETAILS.last_update_date ,'GMT','EDT'),'Month DD, YYYY HH:Mi:SS AM','nls_date_language=American')
TO_CHAR(SYSTIMESTAMP AT TIME ZONE FND_GLOBAL.TIMEZONE, 'MM/DD/YYYY HH:MI:SS AM TZD') - User Preferences Timezone
Date Time Ex: Mon, 13-MAY-2019 03:45:15 GMT
select TO_CHAR(sysdate, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') || ', ' || TO_CHAR(sysdate, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') || ' ' || to_Char(cast(sysdate AS TIMESTAMP) AT TIME ZONE 'GMT', 'hh:mi:ss') || ' GMT' DATE_TIME
from dual