Azure cosmos db time difference in on-premises SQL server

Question

Azure cosmos db time difference in on-premises SQL server

Jakeer Shaik 106

Team,

We are facing a weird issue while loading data from Azure cosmos db to on-premises sql server. Below is the background on the issue

we have two sources (D365 FO and Azure Cosmos db) and two targets (Dedicated SQL & On-premise sql). We load both sources data into both targets using azure synapse pipelines. we designed one dynamic pipeline to load all tables.

D365 - UTC Time Zone , Azure Cosmos db - UTC, Dedicated - UTC & on-premise sql - Mountain Time (UTC -7)

When we load the data from D365 to Dedicated and on-premises, it loads properly. The dates are loaded exactly what is there in source D365.
However, when we try to load from Azure Cosmos db to Dedicated, it works as expected. But, Azure cosmos to on-premises, date columns are reduced 7 hours.

We are not performing any transformation in both the loads. It is just a straight load (using copy activity).

It would be great if someone though some light on this. Why this is behaving like this and is there a solution for this.

Thanks,
Jakeer. Shaik

1 answer

Your answer

Answer 1

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

Hi @Jakeer Shaik ,

Thank you for posting query in Microsoft Q&A Platform.

When you get dates from different time zones and then try to store them in different time zones then system automatically add the offset and store it. In this case since you are getting UTC time zone data and storing it into Mountain Time (UTC-7) time zone column, system automatically reduces to 7 hours.

For now, it looks its working good only. Any specific reason to not have offset? Between, what is the datatype of your column in onprem SQL?

For some reason, If you want to take datatime value as it is even in Mountain time then Consider either of below approaches.

you can consider having trigger running on that table to convert them using TODATETIMEOFFSET. Check this video which explains about Timezones and DATETIMEOFFSET datatype in SQL.

OR

You can have your column datatype as string if thats okay for you.

Hope this helps.

-----------------

Please consider hitting Accept Answer button. Accepted Answers help community as well.

Jakeer Shaik 106 Reputation points

2023-01-03T09:19:17.503+00:00

Hi Maheer,

Thanks for the response. Our column datatype is datetime2(7).

If we don't save it as is, then there will be differences between date field values of source and target systems. We don't see the same behavior in Azure Synapse dedicated sql pool. Though we receive datetime with offset, it stores as is without offset reduction.

Thanks,
Jakeer. Shaik
Jakeer Shaik 106 Reputation points

2023-01-05T13:15:48.227+00:00

Hi @ShaikMaheer-MSFT ,

Any update on this?

As a work around, I am excluding the offset from cosmos and loading it into on-premises. This works fine.

Please do suggest any other better work arounds.

Thanks,
Jakeer. Shaik
Jakeer Shaik 106 Reputation points

2023-01-09T13:09:52.507+00:00

Hi @ShaikMaheer-MSFT

It would be great if you share your observations on this.

Thanks,
Jakeer Shaik
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-01-09T14:05:04.717+00:00

yes this works fine i feel. Hope its not causing issue.

Please consider hitting Accept Answer. Accepted answers help community as well.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-01-12T08:11:22.2866667+00:00

Hi @Jakeer Shaik ,

Just checking if above helps. If yes, please consider marking it as Accepted Answer. Accepted answers help community as well. Please let me know if any further queries.

Share via

Azure cosmos db time difference in on-premises SQL server

1 answer

Your answer