In this article we will look into the most confusing part of dealing with multiple timezones.
All the database datetime fields will be converted to UTC time and stored in the database irrespective of the user timezone.
In order to make the application available globally, Oracle has come up with this UTC approach. All Datetime fields like LAST_UPDATE_DATE, CREATION_DATE will have datetime in the UTC timezone only.
Let’s run a quick query for sysdate and confirm our understanding.
SELECT SYSDATE FROM DUAL
Current UTC time google query:
The effective date and other dates which we input on the application won’t get converted to UTC. Only the datetime fields will get stored with UTC date and time.
If data is entered at 5:01 pm PST on a Thursday, it will have a timestamp in Universal Time Code (UTC) of Friday 12:01 am, but the Effective_Date of the change will be Thursday.
If we want to query the changes that happened in the last 24 hours we normally write query as effective_date between sysdate-24 and sysdate.
However it might not always provide the right output because of the time zone issues. It’s better to use last_update_date when checking for changes as it holds the UTC time.
If there is a requirement to fetch the changes that happened from 07/22/2020 2AM to 07/23/2020 2AM EST, then it becomes tricky to do the comparison as the database holds date time in different format.
UTC (Coordinated Universal Time) and GMT (Greenwich Mean Time) both are one and the same
So, we will use the GMT and convert the last_update_date into EST time zone and do the comparison.
We will use a function by name NEW_TIME which takes the source and target timezones. Click here to find more details on the function.
NEW_TIME function Syntax:
NEW_TIME( date, zone1, zone2 )
zone1 – Original time zone that date is currently displayed in. It can be a value from the table below.
zone2 – New time zone that you wish to display result. It can be one of the values in the following table:
Here is the list of timezones this function accepts:
- AST, ADT: Atlantic Standard or Daylight Time
- BST, BDT: Bering Standard or Daylight Time
- CST, CDT: Central Standard or Daylight Time
- EST, EDT: Eastern Standard or Daylight Time
- GMT: Greenwich Mean Time
- HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
- MST, MDT: Mountain Standard or Daylight Time
- NST: Newfoundland Standard Time
- PST, PDT: Pacific Standard or Daylight Time
- YST, YDT: Yukon Standard or Daylight Time
Query to convert SYSDATE from UTC/GMT to EST timezone.
select to_char(NEW_TIME(sysdate ,'GMT','EST'),'YYYY-MM-DD HH24:MI:SS') from dual
Current EST Time:
On similar lines if we write query to fetch details using last update date, here is how the query will look like:
SELECT * FROM PER_PERIODS_OF_SERVICE WHERE to_char(NEW_TIME(last_update_date,'GMT','EST'),'YYYY-MM-DD HH24:MI:SS') between '2020-07-22 02:00:00' and '2020-07-23 02:00:00'
This query will convert last update date from GMT to EST and then does the comparison with 07/22/2020 2AM and 07/23/2020 2AM and produces the results.
We can also use the below sample queries to fetch dates in different formats.
SELECT TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE)) effective_date, to_char(TRUNC(cast(cast(PER.LAST_UPDATE_DATE as timestamp) at time zone 'America/New_York' as date)) , 'YYYY-MON-DD HH24:MM') as Person_Upd_Date, TO_CHAR(hdr.dei_attribute_date2, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') date2, to_char(ppos.actual_termination_date, 'Mon YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') term_date TO_CHAR(sysdate, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') DDMONYYYY from <tables list>
Hope this has helped you clear the air on the timezones issue. I hope you will be super confident next time when you have to write queries/address issues which arise because of the timezones.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM