Thursday, November 30, 2017

Conversions between timestamps and UTC

In order to convert a timestamp across timestamps , i've found the following statements to be useful.

select SESSIONTIMEZONE, to_char(tx_timestamp,'yyyymmddhh24missff3') ,
to_char(tx_timestamp,'yyyymmddhh24missff3 TZH:TZM' )
, tx_timestamp ,
from_tz(to_timestamp(to_char(tx_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') , 'UTC')
at time zone 'America/Chicago' as ts_converted,
sys_extract_utc(tx_timestamp) as utc,
to_char( sys_extract_utc(tx_timestamp), 'yyyymmddhh24mi' ),
from_tz(to_timestamp(to_char(sys_extract_utc(tx_timestamp),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') , 'UTC')
at time zone 'America/Chicago' as ts_converted1

select to_char((CAST((FROM_TZ(CAST(TO_DATE(:in_gmt_timestamp,'YYYYMMDD HH24MISS') AS TIMESTAMP),
  'GMT')AT LOCAL) AS DATE)),'yyyymmdd-hh24miss')
  into ut_local_timestamp
  FROM DUAL;

No comments:

Post a Comment