Scheduled Job start_date and time zones

ThurlowD

Our department is using Schema Compare for Oracle to detect differences between our Git repository built using Source Control for Oracle and our development database.

We seem to be having a problem as we recently corrected our time zones on the scheduler jobs start_dates to handle daylight savings time and the Schema Compare difference report html is picking up multiple of these "Jobs" from the database with the report showing the start_date property shifted time by an hour incorrectly (by a presumably incorrect -7 time zone offset, instead of our current -6 offset in MDT, though this is depending on the value of start_date). Oddly, Source Control for Oracle extracts from the same database and does not appear to shift this time for the same job for versioning. This results in differences being reported, that seemingly can't be corrected by versioning using the product outputs.

In Oracle the start_date of DBMS_SCHEDULER job is a TIMESTAMP WITH TIMEZONE data type. Redgate products appear to be treating this as a DATE type by default so don't extract with time zone region (TZR) information for versioning and the behaviour of the time value extracted seems to be inconsistent as a result. I presume this may also be an issue with end_date, which is also a TIMESTAMP WITH TIMEZONE type.

All this to ask, is there a way to configure SCO properly so the time zone of the start_date is accounted for in settings or other configuration when doing comparisons and outputting reports and scriptFiles? Or is there something I'm missing that would make the date based extract be more consistent?

Peter_Laws

Hi ThurlowD,

Thanks for raising this, I discovered the issue a little while ago and raised it internally. Further investigation has revealed strangely the issue is caused when a direct connection type is used which results in the timezone not being correctly observed. We've raised the issue with the manufacturer and they have been able to reproduce our findings.

This naturally has fairly far reaching and disruptive effects so we're very keen on resolving it quickly, I'll update this thread as soon as we know more.

Aliya77

ThurlowD said:

Our department is using Schema Compare for Oracle to detect differences between our Git repository built using Source Control for Oracle and our development database.

We seem to be having a problem as we recently corrected our time zones on the scheduler jobs start_dates to handle daylight savings time and the Schema Compare difference report html is picking up multiple of these "Jobs" from the database with the report showing the start_date property shifted time by an hour incorrectly (by a presumably incorrect -7 time zone offset, instead of our current -6 offset in MDT, though this is depending on the value of start_date). Oddly, Source Control for Oracle extracts from the same database and does not appear to shift this time for the same job for versioning. This results in differences being reported, that seemingly can't be corrected by versioning using the product outputs.

In Oracle the start_date of DBMS_SCHEDULER job is a TIMESTAMP WITH TIMEZONE data type. Redgate products appear to be treating this as a DATE type by default so don't extract with time zone region (TZR) information for versioning and the behaviour of the time value extracted seems to be inconsistent as a result. I presume this may also be an issue with end_date, which is also a TIMESTAMP WITH TIMEZONE type.

All this to ask, is there a way to configure SCO properly so the time zone of the start_date is accounted for in settings or other configuration when doing comparisons and outputting reports and scriptFiles? Or is there something I'm missing that would make the date based extract be more consistent?

Hi

Thank you so much for the information.

Aliya77

ThurlowD said:

Our department is using Schema Compare for Oracle to detect differences between our Git repository built using Source Control for Oracle and our development database.

We seem to be having a problem as we recently corrected our time zones on the scheduler jobs start_dates to handle daylight savings time and the Schema Compare difference report html is picking up multiple of these "Jobs" from the database with the report showing the start_date property shifted time by an hour incorrectly (by a presumably incorrect -7 time zone offset, instead of our current -6 offset in MDT, though this is depending on the value of start_date). Oddly, Source Control for Oracle extracts from the same database and does not appear to shift this time for the same job for versioning. This results in differences being reported, that seemingly can't be corrected by versioning using the product outputs.

In Oracle the start_date of DBMS_SCHEDULER job is a TIMESTAMP WITH TIMEZONE data type. Redgate products appear to be treating this as a DATE type by default so don't extract with time zone region (TZR) information for versioning and the behaviour of the time value extracted seems to be inconsistent as a result. I presume this may also be an issue with end_date, which is also a TIMESTAMP WITH TIMEZONE type.

All this to ask, is there a way to configure SCO properly so the time zone of the start_date is accounted for in settings or other configuration when doing comparisons and outputting reports and scriptFiles? Or is there something I'm missing that would make the date based extract be more consistent?

Hi guys

Thank you so much for the information.