Wednesday, February 1, 2017

How to display a SYSTIMESTAMP data type as a TIMESTAMP WITH TZ

To display a value of datatype SYSTIMESTAMP as TIMESTAMP WITH TIME ZONE, use TO_CHAR first, then convert to TIMESTAMP WITH TIMEZONE.

The last expression adds one hour, which can be done after the conversion between the datatypes is complete:
select  TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')+1/24
from dual;
systimestamp TIMESTAMP_TZ TIMESTAMP_TZ + 1hr
2017/02/01 10:36:28.158921 +01:00 01/02/2017 10:36:28,158921000 +01:00 01.02.2017 11:36:28

No comments:

Post a Comment