SQL Query to find changes within a specific timeframe
SQL Query to find out changes within a specific timeframe

 403 total views

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

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply