Column to update the sysdate/time in PST

Question

I have two columns (Status & Updated Date) in IG. Everytime the status column is change with something, I want to log the time it is updated (in PST). Here is how I'm doing it but its not working as expected.

I have created a dynamic action on my updated date column to set the value when it is changed. However, my updated column shows some incorrect time/date.

SELECT
  TO_CHAR(NEW_TIME( TO_DATE( CAST(SYSDATE as DATE), 'MM-DD-YYYY HH24:MI:SS' ),  'GMT', 'PST' ), 'MM-DD-YYYY HH24:MI:SS') TIME_IN_PST
FROM
  TRANSITION_TASKS_NEW;

enter image description here

Answer 1

SYSDATE is already a date so you do not need to use CAST on it. Similarly, since it is already a date you do not need to use TO_DATE on it and this is likely to cause errors.

So your code can just be:

SELECT TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'PST' ), 'MM-DD-YYYY HH24:MI:SS')
         AS TIME_IN_PST
FROM   DUAL;

However, you can make it even simpler using SYSTIMESTAMP (which returns a TIMESTAMP data type) and then you do not need to use the NEW_TIME function:

SELECT TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'PST', 'MM-DD-YYYY HH24:MI:SS')
         AS TIME_IN_PST
FROM   DUAL;

Which both output:

TIME_IN_PST
03-03-2022 08:03:27

db<>fiddle here

---

As for what is causing your code to be incorrect:

The TO_DATE function takes a string as the first argument. When you do:

TO_DATE(SYSDATE, 'MM-DD-YYYY HH24:MI:SS' )

Then Oracle will implicitly convert it to:

TO_DATE(
  TO_CHAR(
    SYSDATE,
    ( SELECT value
      FROM   NLS_SESSION_PARAMETERS
      WHERE  parameter = 'NLS_DATE_FORMAT')
  ),
  'MM-DD-YYYY HH24:MI:SS'
)

If the NLS_DATE_FORMAT session parameter is not MM-DD-YYYY HH24:MI:SS (and the default NLS date format never matches that) then you will get either an error or, worse, an unexpected and probably invalid result.

For example:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-RR';

SELECT TO_CHAR(
         NEW_TIME(
           TO_DATE(SYSDATE, 'MM-DD-YYYY HH24:MI:SS' ),
           'GMT',
           'PST'
         ),
         'MM-DD-YYYY HH24:MI:SS'
       ) AS TIME_IN_PST
FROM   DUAL;

Outputs:

TIME_IN_PST
03-02-0022 16:00:00

Which has the wrong time component AND the wrong century!

To solve this, do not use TO_DATE on values that are already DATEs.

db<>fiddle here