Forum Discussion

mkjit256's avatar
mkjit256
Icon for Helper IV rankHelper IV
1 year ago

Copy Activity in Data Pipeline TimeZone defaulting to UTC

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

Need Help
Pipeline
Show and Tell

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago
    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's avatar
      mkjit256
      Icon for Helper IV rankHelper IV
      1 year ago

      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. 

       

       

  • jwinchell40's avatar
    jwinchell40
    Icon for Resolver III rankResolver III
    1 year ago

    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's avatar
      mkjit256
      Icon for Helper IV rankHelper IV
      1 year ago

      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's avatar
        jwinchell40
        Icon for Resolver III rankResolver III
        1 year ago

        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.