How to display CST or CDT after the date & time, like shown below.

Sep 09 2022 10:30:32 PM CDT

Thanks.

CodePudding user response：

Try something like this...

SELECT 
  To_Char(To_TimeStamp_TZ(To_Char(SYSDATE, 'Mon dd yyyy hh24:mi:ss'), 'Mon dd yyyy hh24:mi:ss ' || 'TZH:TZM'), 'Mon dd yyyy hh24:mi:ss ') ||
  CASE To_Char(To_TimeStamp_TZ(To_Char(SYSDATE, 'Mon dd yyyy hh24:mi:ss'), 'Mon dd yyyy hh24:mi:ss ' || 'TZH:TZM'), 'TZH:TZM') 
      WHEN '-05:00' THEN  'CDT'
      WHEN '-06:00' THEN  'CST'
      -- ... ...
      WHEN ' 01:00' THEN  'CET'
      WHEN ' 02:00' THEN  'EET'
      -- ... ... ...
  ELSE
      To_Char(To_TimeStamp_TZ(To_Char(SYSDATE, 'Mon dd yyyy hh24:mi:ss'), 'Mon dd yyyy hh24:mi:ss ' || 'TZH:TZM'), 'Mon dd yyyy hh24:mi:ss TZH:TZM')
  END "DTM"
FROM DUAL

.... which in my case results as

--  DTM                       
--  ---------------------------
--  Oct 13 2022 19:19:03 EET  

... OR if I exclude WHEN ' 02:00' from case expresssion (forcing result to ELSE part)

--  DTM                       
--  ---------------------------
--  Oct 13 2022 19:22:07  02:00

More about To_TimeStamp_TZ() function at: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions194.htm

Time zone definitions at: https://greenwichmeantime.com/time-zone/definition/
Regards...

CodePudding user response：

Start with a TIMESTAMP WITH TIME ZONE data type of TIMESTAMP '2022-09-09 22:30:32 US/CENTRAL' and then format it using TO_CHAR and the format model 'Mon DD YYYY HH12:MI:SS AM TZD':

SELECT TO_CHAR(
         TIMESTAMP '2022-09-09 22:30:32 US/CENTRAL',
         'Mon DD YYYY HH12:MI:SS AM TZD',
         'NLS_DATE_LANGUAGE=American'
       ) AS ts
FROM   DUAL;

Which outputs:

If you have a DATE then cast it to a timestamp and use FROM_TZ to give it the US/CENTRAL time zone and then convert it to a string as above:

SELECT TO_CHAR(
         FROM_TZ(
           CAST(
             DATE '2022-09-09'   INTERVAL '22:30:32' HOUR TO SECOND
             AS TIMESTAMP
           ),
           'US/CENTRAL'
         ),
         'Mon DD YYYY HH12:MI:SS AM TZD',
         'NLS_DATE_LANGUAGE=American'
       ) AS ts
FROM   DUAL;

Which also outputs:

fiddle