• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:July 24, 2020
  • Reading time:2 mins read
SQL Query to find changes within a specific timeframe
SQL Query to find changes within a specific timeframe
- SQL Query to find changes within a specific timeframe
Total Visits: 7
Todays Visits: 0

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.


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')
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