topic Re: Copy Activity in Data Pipeline TimeZone defaulting to UTC in Pipelines

Copy Activity in Data Pipeline TimeZone defaulting to UTC

mkjit256 — Fri, 25 Oct 2024 14:20:04 GMT

Hello Everyone,

i did use the Copy Activity in a Data Pipeline to copy data from an on premise oracle db that has a timezone ("America/NewYork") to a Fabric lakehouse. In the source tab of the Copy Activity i had chosen a query as the Use Query option, where i am running select SQL statements with some cast of number data columns and creating some concatenated tables. What i am seeing after the copy is completed, is that my date columns are having a UTC time zone . How can i fix that?

Note: I have already updated the OnPremise Gateway to the latest version 3000.242.8 following the post: here however this didn't solve my problem

Re: Copy Activity in Data Pipeline TimeZone defaulting to UTC

lbendlin — Fri, 25 Oct 2024 21:58:29 GMT

an on premise oracle db that has a timezone ("America/NewYork")

That is rather unusual. Servers and databases should by default run on UTC.

In your Select statement can you cast the dates to UTC?

Re: Copy Activity in Data Pipeline TimeZone defaulting to UTC

jwinchell40 — Mon, 28 Oct 2024 02:23:35 GMT

mkjit256 - Is the data coming out of the server as datetime + TZ or just as a datetime? Is the time component actually getting changed or is the TZ Offset just getting added to the end of the datetime?

If it is just coming out as a datetime, can you convert it to the datetime + timezone in the soure query coming out of Oracle?

Depending on the answers above, one thing you can do is just write the TZ conversion in the source/destination mapping of the Copy data.

Jeremy

Re: Copy Activity in Data Pipeline TimeZone defaulting to UTC

mkjit256 — Mon, 28 Oct 2024 14:40:54 GMT

Thanks for your reply, am i getting the timezone wrong? i had got the America/NewYork fro running SELECT SESSIONTIMEZONE FROM DUAL. I am also getting a value of -04:00 When running: SELECT DBTIMEZONE FROM DUAL;

Both commands are run from SQL oracle developer app.

The values that i am seeing in SQL oracle developer app are:
2024-Apr-09 9:17:32 AM

however in the lakehouse table what i am seeing is:

2024-04-09 13:17:32.000000

It is clear that the time in the lakehouse is 4 hours after the time in DB, which aligns with the -04:00 (America/New_York) answer i am getting.

In my select statement that i am running to copy the table from source (oracle Db) to destination (lakehouse) is not casting dates to utc.

Re: Copy Activity in Data Pipeline TimeZone defaulting to UTC

mkjit256 — Mon, 28 Oct 2024 14:47:08 GMT

Hello jwinchell40 . the datetime in the source (oracle database) has no +TZ, it is under the format of: 2024-Apr-09 9:17:32 AM.
What i am seeing is that the time component is getting changed. The datetime i am seeing in the lakehouse is: 2024-04-09 13:17:32.000000.

If it is just coming out as a datetime, can you convert it to the datetime + timezone in the soure query coming out of Oracle?

Can you explain more on how to do that. Thanks.

Re: Copy Activity in Data Pipeline TimeZone defaulting to UTC

jwinchell40 — Fri, 01 Nov 2024 18:54:09 GMT

mkjit256 - Sorry for the delay. Let me see if I can find the Oracle equivalent of:

SELECT <date> AT TIMEZONE 'EASTERN STANDARD TIME' AT TIMEZONE 'UTC' AT TIMEZONE 'EASTERN STANDARD TIME'

I don't recall if I can go straight to EST when adding the TZ for the first time or if I have to Cast/Recast it accordingly.