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

If this is a new application, I'd look at using a different datatype. With the datatype ```TIMESTAMP WITH LOCAL TIME ZONE``` the data is stored as a UTC date internally but at runtime it's displayed in the timezone of the user session. — Koen Lostrie, Mar 04 '22 at 17:32

MT0 · Accepted Answer · 2022-03-03T16:13:15.583

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

When I tried the code, the timestamp is accurate in PST, however the date is always appearing as "03-02-2022" — VamC, Mar 06 '22 at 08:42
@VamC [Edit](https://stackoverflow.com/posts/71339973/edit) your question to include a [MRE] with: the `CREATE TABLE` statement for your table; the `INSERT` statements for the sample data; the `SELECT` statement you are using to display the data; your current NLS session parameters for dates/timestamps; and the expected output. Without knowing what you are putting in, what you expect out and what your session parameters are its difficult to determine. — MT0, Mar 09 '22 at 15:47
I have updated the screenshot of my apex view - Under dynamic actions, you'll see the code I'm using. Currently it returns the accurate PST time though, however the date is incorrect. — VamC, Mar 11 '22 at 16:28

Column to update the sysdate/time in PST

1 Answers1