How to deal with Datetimes stored in Database as UTC timezone?

Let’s run a quick query for sysdate and confirm our understanding.

SELECT SYSDATE FROM DUAL

Query output:

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.

Sample Usecase:

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

Query Output:

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.