Data Base SystemTime in UTC TimeZone (Oracle)

We have developed a DB function to change date from one time zone to another time zone (With the help of Google J). Here is the function.

CREATE OR REPLACE FUNCTION CONVERT_TIME ( datetime IN TIMESTAMP, tz1 IN VARCHAR2, tz2 IN VARCHAR2 )

return TIMESTAMP WITH TIME ZONE

AS

retval TIMESTAMP WITH TIME ZONE;

BEGIN

retval := from_tz(datetime, tz1) at time zone tz2;

return retval;

END;

If we want to convert Date in the DB time zone to some other time zone then use following query.

SELECT CONVERT_TIME(SYSTIMESTAMP, SESSIONTIMEZONE ,'GMT') FROM DUAL;

If we want to convert date in known time zone to some other time zone then pass appropriate value in tz1 parameter of the function.

We will deploy this db function in ICP 2.1 release. Anyone can use this function for their development. Please let me know if you need more information.

Examples:

--------- 

INSERT INTO JOSSO_ROLE

(name, datetime) 

values ('verma', CONVERT_TIME(SYSTIMESTAMP, SESSIONTIMEZONE ,'GMT'));