Sudhendu Sharma Sudhendu Sharma - 13 days ago 9
SQL Question

Oracle Timestamp UTC time format

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

Maybe something I am missing but what I don't know?
Is there something in client side I need to do?

Answer

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.

Comments