Why do oracle triggers show a different timestamp what is being inserted

Question

My table has a Trigger that stores current_timestamp into a field when the row in the table is updated.

When a row is first inserted it is inserted with a Created date equal to Current_Timestamp. It appears when the procedure inserts the created_date it is given the correct time for daylight savings time but the trigger is inserting the incorrect time.

So the records in question are showing a created date of '2018-03-11 03:13:53.392086000' and a modified date of '2018-03-11 02:13:53.392086000'.

To add to this, we use goldengate for replication and it is using '2018-03-11 03:13:53.392086000' as the audit timestamp and not '2018-03-11 02:13:53.392086000'

Why would a trigger show a time differently then goldengate and the insert? database version I am working with is Oracle 11.2.0.4.0.

Trigger that handles the insert modified time

create or replace trigger UT_INSERT before insert on MYTABLE
 REFERENCING NEW AS NEWROW OLD AS OLDROW FOR EACH ROW
 begin
    :NEWROW.MODIFIED_TIMESTAMP := CURRENT_TIMESTAMP;
 end;

Trigger for updated modified time

create or replace trigger UT_UPDATE before update on MYTABLE
 REFERENCING NEW AS NEWROW OLD AS OLDROW FOR EACH ROW
 begin
    :NEWROW.MODIFIED_TIMESTAMP := CURRENT_TIMESTAMP;
 end;

Answer 1

The insert statement that is setting created_date is using Current_timestamp. All sessions are currently using a TZ offset of -05:00 and the sessiontimezone is America/Chicago.

That can't always be true. The only way to get the two values you have is for the insert to have been done with created_date set to systimestamp and for the session that issued that to have had it's session time zone set to -05:00, not to America/Chicago.

The reason that must be the case is that there was no 02:13 on March 11th because of the DST change:

select timestamp '2018-03-11 03:13:53.392086000 America/Chicago' from dual;

TIMESTAMP'2018-03-1103:13:53.392086000AMERICA
---------------------------------------------
2018-03-11 03:13:53.392086000 AMERICA/CHICAGO

select timestamp '2018-03-11 02:13:53.392086000 America/Chicago' from dual;

Error report -
SQL Error: ORA-01878: specified field not found in datetime or interval
01878. 00000 -  "specified field not found in datetime or interval"

select timestamp '2018-03-11 02:13:53.392086000 -5:00' from dual;

TIMESTAMP'2018-03-1102:13:53.3920860
------------------------------------
2018-03-11 02:13:53.392086000 -05:00

So assuming the column value was set from your trigger, which seems entirely reasonable, the session which did that must have been -05:00 to be able to have got the timestamp at 02:13; and the created_date must have been using systimestamp, where the server OS had the correct time zone of America/Chicago to pick up the DST change (ignore dbtimezone for this), to have got 03:13 at the same moment in time.

As long as the session time zone and server time zone are the same you won't see a discrepancy between using current_timestamp or systimestamp, but you can't always control how sessions are configured, so a mistake or deliberate time zone change can cause problems like this.

Unless you are specifically trying to record a user's local time for some reason (and then you should probably be using a timestamp data type that preserves the time zone), it's safer to always use systimestamp for all of the values you record; and if you're storing as a plain timestamp then it might be safer to always store UTC time.

(Changing to UTC now, particularly with existing data to worry about, probably isn't an option; and not sure how Golden Gate would handle that but seems like a common scenario. Something to think about for the future though. Changing to always use systimestamp ought to be fairly transparent, except that you have this and probably other discrepancies to worry about. Finding and changing all code that might do inserts could be an issue - but then you seem to have inconsistencies already).