Copy Activity in Data Pipeline TimeZone defaulting to UTC

[mkjit256]

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

[jwinchell40]

@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

[mkjit256]

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.

[jwinchell40]

@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.   

[lbendlin]

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?

[mkjit256]

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.