Options

Scheduled Job start_date and time zones

ThurlowDThurlowD Posts: 1 New member
in Schema Compare for Oracle
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?
Tagged:
· Share on Twitter

Answers

  • Options
    Peter_LawsPeter_Laws Posts: 214 Silver 2
    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.
    Kind regards
    Peter Laws | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • Options
    Aliya77Aliya77 Posts: 3 New member
    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.

    · Share on Twitter
  • Options
    Aliya77Aliya77 Posts: 3 New member
    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.
    · Share on Twitter
Sign In or Register to comment.