Oracle Timestamp UTC time format problem

Question

CREATE TABLE DIALOGUE_TABLE(EXPIRE_TIME TIMESTAMP);

Following code snippet is inside stored proc :-

PO_EXPIRETIME :- OUT PARAM of procedure a varchar2

SELECT TO_CHAR(SYS_EXTRACT_UTC(EXPIRE_TIME)) 
  INTO PO_EXPIRETIME 
  FROM DIALOGUE_TABLE; 

When I run Stored Proc from server using EXEC and print PO_EXPIRETIME time stamp is proper with UTC format.

But when I call stored procedure from OCCI and client the timestamp recieved is not same but that is the actual timestamp in table not UTC formatted.

May be something I am missing but what I dont know? Is there somthing in clientside I need to do?

I am using oracle 11g.

Answer 1

If the column is declared as a TIMESTAMP and not, say, a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE, the timestamp that is stored in the table will not have a time zone component. As a result, SYS_EXTRACT_UTC will convert the timestamp to UTC using the session time zone which is something that is controlled by the client and may be different on different client machines. I suspect that if you run

SELECT SessionTimeZone
  FROM dual;

from your OCCI application and from your SQL*Plus session that you will end up with different results which is causing the strings returned to be different.

If you want the string that is returned to be independent of the client machine, I would tend to suggest storing the time zone in the database column. Is changing the schema definition to use a TIMESTAMP WITH TIME ZONE an option? Barring that, you could ensure that every client machine has the same time zone configured and/or run an explicit ALTER SESSION, i.e.

ALTER SESSION 
  SET time_zone = '-05:00';

in the stored procedure to ensure that the conversion is always done from a particular time zone.