Convert timezone format in Oracle-CodePudding

I need to convert below timezone format in the following format:

Input: 2020-10-28T20:12:20.986Z

Output: 28-OCT-20 8:12 PM

I tried below query but I am unable to get timestamp with it. Please help.

select TO_TIMESTAMP(SUBSTR('2020-04-21T13:02:31.259Z',1,(INSTR('2020-04-21T13:02:31.259Z', 'T') - 1)),'YYYY-MM-DD HH24:MI:SS') from dual;

CodePudding user response：

One option might be this

SQL>  alter session set nls_timestamp_format = 'dd-MON-YY hh:mi PM' ;

Session altered.

SQL> select to_timestamp('2020-10-28T20:12:20.986Z','yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') from dual ;

TO_TIMESTAMP('2020-10-28T20:12:20.986Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
---------------------------------------------------------------------------
28-OCT-20 08:12 PM

SQL>

But if you rely better in the to_timestamp function without any session setting, then it is better

SQL>  select to_timestamp('2020-10-28T20:12:20.986Z','yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') from dual ;

TO_TIMESTAMP('2020-10-28T20:12:20.986Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
---------------------------------------------------------------------------
28-OCT-20 08.12.20.986000000 PM

CodePudding user response：

You have a timestamp string with a time zone, use TO_TIMESTAMP_TZ rather than TO_TIMESTAMP and then use TO_CHAR to format it:

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ(
           '2020-04-21T13:02:31.259Z',
           'YYYY-MM-DD"T"HH24:MI:SS.FFTZR'
         ),
         'DD-MON-RR HH12:MI AM',
         'NLS_DATE_LANGUAGE=American'
       )
FROM   DUAL;

db<>fiddle here

Note: DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE are binary data types and are stored in 7-20 bytes (1 byte each for century, year-of-century, month, day, hour, minute and second then up to 6 optional bytes for fractional seconds for TIMESTAMPs and up to 7-bytes for time zone for TIMESTAMP WITH TIME ZONE). It is never stored in any particular format.

How the DATE/TIMESTAMP data types are displayed is dependent on the client application that you are using to query the database; some may use the NLS settings for the user's session but others do not use that. If you want a particular format then convert the DATE/TIMESTAMP to a string using TO_CHAR.