oracle-apexoracle-apex-5.1oracle-apex-5

Column to update the sysdate/time in PST


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


Solution

  • 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