Loading...

XML

Word

Printable

Type: Bug
Resolution: Done
Priority: Major
Fix Version/s: 1.7.0.Beta1
Affects Version/s: None
Component/s: sqlserver-connector
Labels:
- easy-starter

Blocked:
False
Ready:
False
Release Note Text:
Undefined
Target Release:

1.7.GA
Steps to Reproduce:
Hide

Start the test SQL Server container in a timezone different from UTC:

diff --git a/debezium-connector-sqlserver/pom.xml b/debezium-connector-sqlserver/pom.xml index fa7fa447b..bea9ef34b 100644 --- a/debezium-connector-sqlserver/pom.xml +++ b/debezium-connector-sqlserver/pom.xml @@ -115,6 +115,7 @@ <SA_PASSWORD>${sqlserver.password}</SA_PASSWORD> <MSSQL_PID>Standard</MSSQL_PID> <MSSQL_AGENT_ENABLED>true</MSSQL_AGENT_ENABLED> + <TZ>America/Los_Angeles</TZ> </env> <ports> <port>${sqlserver.port}:1433</port>

Run some integration tests to populate the CDC schema.

Run the following query in the test database:

SELECT tran_begin_time, CURRENT_TIMEZONE() AS TZ, tran_begin_time AT TIME ZONE 'UTC' AS UTC FROM testDB.cdc.lsn_time_mapping;

tran_begin_time TZ UTC ----------------------- --------------------------------------- ------------------------------ 2021-04-28 16:26:36.387 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:36.387 +00:00 2021-04-28 16:26:36.360 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:36.360 +00:00 2021-04-28 16:26:43.293 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:43.293 +00:00 2021-04-28 16:26:45.347 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:45.347 +00:00 2021-04-28 16:26:45.333 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:45.333 +00:00 2021-04-28 16:26:45.567 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:45.567 +00:00 2021-04-28 16:31:50.070 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:31:50.070 +00:00 2021-04-28 16:36:55.047 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:36:55.047 +00:00

As you can see, the values in the first and the third columns are the same with the exception of the +00:00 timezone offset added that corresponds to UTC, not PDT.

A proper solution might be to use a conversion like the following:

TODATETIMEOFFSET(?, DATEPART(TZOFFSET, SYSDATETIMEOFFSET())) AT TIME ZONE 'UTC'

In this case, the result will look as expected:

1> SELECT tran_begin_time, 2> CURRENT_TIMEZONE() AS TZ, 3> TODATETIMEOFFSET( 4> tran_begin_time, 5> DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) 6> ) AT TIME ZONE 'UTC' AS UTC 7> FROM testDB.cdc.lsn_time_mapping; 8> GO tran_begin_time TZ UTC ----------------------- --------------------------------------- ------------------------------ 2021-04-28 16:26:36.387 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:36.387 +00:00 2021-04-28 16:26:36.360 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:36.360 +00:00 2021-04-28 16:26:43.293 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:43.293 +00:00 2021-04-28 16:26:45.347 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:45.347 +00:00 2021-04-28 16:26:45.333 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:45.333 +00:00 2021-04-28 16:26:45.567 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:45.567 +00:00 2021-04-28 16:31:50.070 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:31:50.070 +00:00 2021-04-28 16:36:55.047 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:36:55.047 +00:00 2021-04-28 16:42:00.057 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:42:00.057 +00:00 (9 rows affected)

Notes

The proposed solution above is a result of quick googling and may need validation and/or optimization.

The solution above is still error-prone since the conversion will not work as expected if the server timezone changes (e.g. due to DST changes) between a CDC record has been produced and consumed (ref).
Show
Start the test SQL Server container in a timezone different from UTC: diff --git a/debezium-connector-sqlserver/pom.xml b/debezium-connector-sqlserver/pom.xml index fa7fa447b..bea9ef34b 100644 --- a/debezium-connector-sqlserver/pom.xml +++ b/debezium-connector-sqlserver/pom.xml @@ -115,6 +115,7 @@ <SA_PASSWORD>${sqlserver.password}</SA_PASSWORD> <MSSQL_PID>Standard</MSSQL_PID> <MSSQL_AGENT_ENABLED> true </MSSQL_AGENT_ENABLED> + <TZ>America/Los_Angeles</TZ> </env> <ports> <port>${sqlserver.port}:1433</port> Run some integration tests to populate the CDC schema. Run the following query in the test database: SELECT tran_begin_time, CURRENT_TIMEZONE() AS TZ, tran_begin_time AT TIME ZONE 'UTC' AS UTC FROM testDB.cdc.lsn_time_mapping; tran_begin_time TZ UTC ----------------------- --------------------------------------- ------------------------------ 2021-04-28 16:26:36.387 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:36.387 +00:00 2021-04-28 16:26:36.360 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:36.360 +00:00 2021-04-28 16:26:43.293 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:43.293 +00:00 2021-04-28 16:26:45.347 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:45.347 +00:00 2021-04-28 16:26:45.333 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:45.333 +00:00 2021-04-28 16:26:45.567 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:26:45.567 +00:00 2021-04-28 16:31:50.070 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:31:50.070 +00:00 2021-04-28 16:36:55.047 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 16:36:55.047 +00:00 As you can see, the values in the first and the third columns are the same with the exception of the +00:00 timezone offset added that corresponds to UTC, not PDT. A proper solution might be to use a conversion like the following: TODATETIMEOFFSET(?, DATEPART(TZOFFSET, SYSDATETIMEOFFSET())) AT TIME ZONE 'UTC' In this case, the result will look as expected: 1> SELECT tran_begin_time, 2> CURRENT_TIMEZONE() AS TZ, 3> TODATETIMEOFFSET( 4> tran_begin_time, 5> DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) 6> ) AT TIME ZONE 'UTC' AS UTC 7> FROM testDB.cdc.lsn_time_mapping; 8> GO tran_begin_time TZ UTC ----------------------- --------------------------------------- ------------------------------ 2021-04-28 16:26:36.387 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:36.387 +00:00 2021-04-28 16:26:36.360 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:36.360 +00:00 2021-04-28 16:26:43.293 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:43.293 +00:00 2021-04-28 16:26:45.347 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:45.347 +00:00 2021-04-28 16:26:45.333 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:45.333 +00:00 2021-04-28 16:26:45.567 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:26:45.567 +00:00 2021-04-28 16:31:50.070 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:31:50.070 +00:00 2021-04-28 16:36:55.047 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:36:55.047 +00:00 2021-04-28 16:42:00.057 (UTC-08:00) Pacific Time (US & Canada) 2021-04-28 23:42:00.057 +00:00 (9 rows affected) Notes The proposed solution above is a result of quick googling and may need validation and/or optimization. The solution above is still error-prone since the conversion will not work as expected if the server timezone changes (e.g. due to DST changes) between a CDC record has been produced and consumed ( ref ).
Market:

SFDC Cases Counter:
SFDC Cases Open:
SFDC Cases Links:

As a solution to ~~DBZ-1643~~, the SQL Server connector attempts to convert transaction times to UTC using the AT TIME ZONE 'UTC' expression.

It doesn't seem to work because the lsn_time_mapping table stores only the local time of the transaction, and according to the SQL Server documentation,

When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone.

As a result, if a server runs say in UTC-4, the connector metrics always show it as 4 hours behind the source.

Assignee:: Unassigned

Reporter:: Sergei Morozov (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Created:: 2021/04/29 12:08 AM

Updated:: 2023/01/24 3:42 PM

Resolved:: 2021/08/17 8:17 PM

Details

Notes

Description

Attachments

Easy Agile Planning Poker

Activity

People

Dates