Convert date from UTC to local time in report

Article Id: 39375
Status: Published
Updated On: 14-02-2018 07:55
Legacy Id: TEC1803888
Products:
CA Harvest Software Change Manager - OpenMake Meister
Issue/Introduction:

Problem: 

I'm writing a BIRT report to show modification history for a selected form and the date comes back in UTC format.  I want the report to be in local Eastern time format for easier reading. How can I convert the date/time. Here is my query:

SELECT REALNAME as "Modified By",
  FORMNAME as "Package Form name",
  EXECDTIME as "Modified Date",
  ACTION as "Action"
FROM (HARFORMHISTORY
      INNER JOIN HARFORM
      ON HARFORMHISTORY.FORMOBJID=HARFORM.FORMOBJID)
  INNER JOIN HARUSER
  ON HARFORMHISTORY.USROBJID=HARUSER.USROBJID
WHERE HARFORM.FORMNAME='$formname'
ORDER BY HARFORMHISTORY.EXECDTIME

This query shows execdtime as: 03-13-2016;05:46:53.  I'd like the report to show execdtime as: 03-13-2016;12:46:53 am or 03-13-2016;00:46:53.

Environment:  

CA Harvest SCM all versions, using the Oracle DBMS

Cause: 

Oracle stores all dates in the Harvest database in UTC (GMT) time.  When writing SQL queries and creating reports, you will need to include some additional function calls and formatting if you prefer to display dates in your local time zone.

Resolution:

The following will return local time rather than GMT:

TO_CHAR(FROM_TZ(CAST(HARFORMHISTORY.EXECDTIME AS TIMESTAMP), 'GMT') at time zone 'US/Eastern', 'MM-DD-YYYY HH:MI:SS')

To "unpack" this statement, here’s what the above will do:

  • CAST(HARFORMHISTORY.EXECDTIME AS TIMESTAMP) will convert the date field to a "timestamp" data type
  • FROM_TZ( … , 'GMT') will identify the original time zone of the date field
  • at time zone 'US/Eastern' will convert from GMT to US/Eastern time zone
  • TO_CHAR( … , 'MM-DD-YYYY HH:MI:SS') will format the date and time information to make it more readable.

So, your query would now look something like this:

SELECT REALNAME as "Modified By",
  FORMNAME as "Package Form name",
  TO_CHAR(FROM_TZ(CAST(EXECDTIME AS TIMESTAMP), 'GMT') at time zone 'US/Eastern', 'MM-DD-YYYY HH:MI:SS')  as "Modified Date",
  ACTION as "Action"
FROM (HARFORMHISTORY
      INNER JOIN HARFORM
      ON HARFORMHISTORY.FORMOBJID=HARFORM.FORMOBJID)
  INNER JOIN HARUSER
  ON HARFORMHISTORY.USROBJID=HARUSER.USROBJID
WHERE HARFORM.FORMNAME='$formname'
ORDER BY HARFORMHISTORY.EXECDTIME

Additional Information:

More information on how to convert time zones, and other formatting details for Oracle SQL queries can be found in the documentation for your version of the Oracle database.

Environment:

Release: SCMNCR99000-12.5-Harvest-Software Change Manager-Named User-Competitive Replacem
Component: