QUESTION::PIPELINE::COPY DATA::MAPPING::DATETIMEOFFSET FORMAT

[Element115]

QUESTION:

Does anyone know how to specify correctly the DateTimeOffset format of Copy data activity?

ISSUE:

The data source is on EST time and the Copy data activity should load the data into a LH with all timestamps in EST timezone and not UTC.  Therefore I changed the Mapping settings of the Copy data activity like so:

DateTimeOffset format --> yyyy-MM-dd HH:mm:ss-04:00

But it didn't do anything.  All timestamps loaded into the LH are still in UTC timezone.

Am I missing "" marks around the yyyy-MM-dd HH:mm:ss-04:00 string?  And if not, is it a bug or is there another syntax to use for this?

And by the way, having a help link redirect to a .NET documention page for this is not really helpful because it still does not tell you whether or not the settings field in the Copy data activity expects the string to be surrounded by single or double quote marks.  Tiral and error takes precious time away from the business task at hand.

[davecmcminoway]

THIS!! Ive spent hours on try/repeat to get the correct date time. Run in Oracle and I see the correct timedate. But into the lakehouse and its Pacific mountain. Ive tried multple things and the only thing I can find that works is : from_tz(to_timestamp(to_char(CURRENT_TIMESTAMP , 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'), 'UTC') at time zone 'America/New_York'. Trying now to cahnge all hundereds of queries to include this. 

Elsewhere I also have to explicitly convert and format within data pipelines. 

[dbrowne]

Thanks for reporting this.  The product team has decided that this behavior is a bug, and Copy Data task should not be modifying datetime/datetime2 values.  This behavior will be changed in a future release of the On Prem Data Gateway.

[matkvaid]

Last OPDG release was a week ago, so a week after You found this bug. But still no solution for this? In my case, all dates and times are in UTC and time zone is +2, so i get all results with -2 hours. Date fields with T00:00:00 are converted to -1 day T22:00:00....

[Element115]

Same here.  But I discovered this bug before last week and notified Microsoft, who didn't seem to be aware.  But to make matters worse, it's not just that all timestamps are converted to UTC, half of them are converted to the wrong UTC, so even using a view on the source to shift the timestamps won't work because the conversion, at this point in time when DST is off, will convert all timestamps to UTC with DST off, meaning also those timestamps that should be converted with DST on, ie Winter timestamps.

I have a support ticket open already for the past 2 weeks and waiting on a resolution.  Further response on the forum from Microsoft can be found here:

Re: QUESTION::COPY DATA::TIMEZONE ISSUE - Microsoft Fabric Community

[matkvaid]

I got a reply from support, created ticked, that this bug fix should be deployed by the end of this month. Hoping that will solve...

[Element115]

@dbrowne I see... are you implying it is the OPGW that is doing the conversion and not the Copy data activity?

[dbrowne]

The copy task is running on the gateway in this case.  When running in the cloud everything is in UTC time.

[Element115]

Ok thanks. And what is this Mapping option for in the Copy data settings: DateTimeOffset format?

Is it to tell the backend not to convert to UTC but instead use whatever timezone is specified in this setting string?

[dbrowne]

A `datetime2` has no timezone information in it.  Clients might _interpret_ it as being a local time in some timezone, but the Copy Data task will copy the value directy to a timetamp, which also has no timezone information.

Test by loading something like this: 

select cast('2024-05-01 12:35' as datetime2(0)) dt

I suspect whatever tool you're using to examine the data is misleading you, or the view is doing something strange.

[Element115]

@dbrowne Any update?

[Anonymous]

JHi @Element115 ,

Can you please reach out to our support team so they can take a closer look - Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

[Element115]

Case support # 2405090040010828

[Element115]

Sure but could you please tell me what this Mapping option for Copy data is used for?  DateTimeOffset format

And how do you specify the format?

[Element115]

I forgot to address your comment re the view doing something strange or my DB tool (DBeaver) misleading me.  One thing that needs to be understood, all that comes into DB already in EST.  So it is not a case where DBeaver grabs this data and converts it from UTC to EST when it displays the result set from some SELECT command I issue.  This is the case for all our DBs which data are then used in PBI reports and show up as EST without any explicit conversion.  

Further, the DDL for the view re the case at hand is as follows:

CREATE   VIEW vMUP_Status 
AS
	SELECT  
		CAST(lastAccessed AS DATE)		AS [Date]
	,	lastAccessed					AS Refresh_Date
	,	statusId						AS ID
	,	display							AS Display
	,	currentLayout					AS Layout
	,	CAST('' AS XML).value(
							'xs:base64Binary(xs:hexBinary(sql:column("MUP_screenshot")))'
						,	'VARCHAR(MAX)'
						)				AS MUP_screenshot 

	FROM dbo.MUP_Status 
	WHERE 
		statusId		IS NOT NULL		AND 
		MUP_screenshot	IS NOT NULL;

So no timezone offset conversion or anything. We grab lastAccessed (type DATETIME2) as it was stored, namely in EST. 

This is why I can only conclude it is something on the backend of Copy data activity that is converting the original data from EST to UTC.  Besides, why would the Copy data activity have a Mapping-->DateTimeOffset format setting?

I just wish someone would tell me why this setting is doing nothing all.  Am I specifying the string wrong? (shown in prev posts)

[Element115]

So here is the test result: first, the Copy data activity Source setup:

Then the result of running this SQL on the on-prem server in the LH:

As you can see, the time part has been shifted 4 hours into the future for some reason.  Under the Mappping tab, all options are empty.  So something is happening under the hood, is it not?

[Element115]

Are you saying that if my DATETIME2 column as date and time in EST, then it will be copied as such, with no changes, by Copy data?

[Anonymous]

Hi @Element115 ,

Thanks for using Fabric Community.

Did you tried with the date functions? It is very easy to do convertions.

@convertFromUtc(utcnow() ,'Eastern Standard Time')

Docs to refer - Expressions and functions - Microsoft Fabric | Microsoft Learn

If you have any format issues, you can also refer this thread - Link

Hope this is helpful. Please let me know incase of further queries.

[Element115]

The problem is that I have a Copy data activity loading from an on-prem DB using this SQL query:

SELECT 
		[Date]
	,	Refresh_Date  <-- this is converted to UTC when it should not be
	,	ID
	,	Display
	,	Layout
	,	MUP_screenshot
FROM 
	vMUP_Status_15M

How do you suggest I use a function in the SQL query?  I don't think it's possible.  I would need to intercept every row that comes back from the source, basically iterate over the result set that Copy data receives back before it loads it into the destination, ie the LH.

I am not aware that Copy data let's you do this.  And quite frankly, having to spin up a Notebook to do this is extra complication and extra CU consumed and that is not acceptable since the Copy data activity has a Mapping setting for DateTimeOffset format.

So what is the proper way of using this DateTimeOffset format setting?

[Anonymous]

Hi @Element115 ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Anonymous]

Hi @Element115 ,

Can you please refer this - sql - Convert Datetime column from UTC to local time in select statement - Stack Overflow