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:
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.