topic Re: Converted timestamps using expression functions always have UTC timezone - is this expected? in Pipelines

Converted timestamps using expression functions always have UTC timezone - is this expected?

DanielAmbler — Tue, 11 Jun 2024 14:49:18 GMT

Hi,

I have some pipeline telemetry i'd like to store as part of a pipeline run. What I'd like to store is the offset between the converted timestamp and utc.

I am currently testing the timezone conversion functions and going through a process of utcNow -> convertFromUtc -> convertTimeZone. All this works well in terms of the timestamp is correct, however all timestamps coming out of these functions have a timezone of Z, which implies UTC (according to ISO 8601 - Wikipedia.)

The reference docs MS Learn - convertFromUtc specifies that time zone information is preserved - is this a bug or expected behaviour?

Thanks in advance.

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

Anonymous — Wed, 12 Jun 2024 09:51:51 GMT

Hi DanielAmbler ,

Thanks for using Fabric Community.
Can you please explain your query little bit? If possible can you please explain with an example?

If you can provide these details, I can guide you better.

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

DanielAmbler — Wed, 12 Jun 2024 13:13:18 GMT

Hi Anonymous ,

Thanks for your reply, sure I can provide more information. Given this test pipeline

The first activity sets a timestamp (@utcnow())

The second converts from utc (

@convertFromUtc(variables('var_utcnow'), 'GMT Standard Time'))

The third converts further between timezones (

@convertTimeZone(variables('var_utc_convert_timezone_1'), 'GMT Standard Time', 'Pacific Standard Time')

The fourth formats the datetime (

@formatDateTime(variables('var_convert_timezone_1_timezone_2'), 'yyyy-MM-ddTHH:mm:ss.fffffffzzz'))

In each case the timezone output (regardless of the step) is always 'Z', which as I understand it indicates no difference between the timestamp and UTC.

I am wanting to understand if this is expected behaviour, and if not, is there a known method of obtaining an offset between a timestamp that is converted from UTC to another zone?

Please let me know if I can provide any more information.

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

Anonymous — Thu, 13 Jun 2024 10:59:37 GMT

Hi DanielAmbler ,

This is an expected behvaiour.
Here are my results when I tried to reproduce your scenario.

{ "name": "var_utcnow", "value": "2024-06-13T10:24:21.7790903Z" } { "name": "var_utc_convert_timezone_1", "value": "2024-06-13T11:24:21.7790903Z" } { "name": "var_convert_timezone_1_timezone_2", "value": "2024-06-13T03:24:21.7790903Z" } { "name": "var_formatted", "value": "2024-06-13T03:24:21.7790903Z" }

Can you please help me understand what are you expecting as output here?

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

DanielAmbler — Thu, 13 Jun 2024 13:55:48 GMT

Hi Anonymous

Just taking a single conversion for simplicity, I guess I was expecting something along the lines of

{

"name": "var_utcnow",

"value": "2024-06-13T10:24:21.7790903Z" # expected as this is UTC

}

{

"name": "var_utc_convert_timezone_1",

"value": "2024-06-13T11:24:21.7790903Z" # Assuming UK as the conversion would expect 2024-06-13T11:24:21.7790903+01:00

}

Regardless of the formating options for timezone, the offset always specifies Z. The document I referenced were

ISO 8601 - Wikipedia

"The UTC offset is appended directly to the time instead of "Z" suffix above; other nautical time zone letters are not used. The offset is applied to UTC to get the civil time in the designated time zone in the format '±[hh]:[mm]', '±[hh][mm]', or '±[hh]'."

and

https://learn.microsoft.com/en-us/fabric/data-factory/expression-language#convertfromutc

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

Anonymous — Fri, 14 Jun 2024 11:28:02 GMT

Hi DanielAmbler ,

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.

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

DanielAmbler — Mon, 17 Jun 2024 12:25:24 GMT

I guess given the documentation, with any converted timestamp I would expect to see the offset from UTC, similar to (see below in red), any timestamps after the first have been converted from UTC to something else, so they must carry an offset from UTC.

{

"name": "var_utcnow",

"value": "2024-06-13T10:24:21.7790903Z"

}

{

"name": "var_utc_convert_timezone_1",

"value": "2024-06-13T11:24:21.7790903Z+01:00"

}

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

Anonymous — Mon, 17 Jun 2024 12:50:39 GMT

Hi DanielAmbler ,

If you observe carefully, you can notice the time difference already added to it (i.e. 1 hr, 10:24 --> 11:24)

{

"name": "var_utcnow",

"value": "2024-06-13T10:24:21.7790903Z"

}

{

"name": "var_utc_convert_timezone_1",

"value": "2024-06-13T11:24:21.7790903Z"

}

But if you are talking about representation or formating of the time, please share this in feedback. At present this is by design.

However, your suggestion is definitely valuable! We use customer feedback like yours to improve the product. The more users who request the ability to customize backgrounds, the higher it moves on our list.

Appreciate if you could share the feedback on our Microsoft Fabric Ideas. Which would be open for the user community to upvote & comment on. This allows our product teams to look into your feedback and improve the product.

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

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

DanielAmbler — Mon, 17 Jun 2024 13:54:38 GMT

Yes, I see that. I know the timestamps are different, by definition then, the UTC offset should also be different.

A timestamp in UTC will have an offset of 0

A timestamp converted to a timezone of +1 hour should have a UTC offset of +1.

This is not the case with the timestamps above. Rregardless of how they are formatted, they all carry an offset of 0 form UTC. Is this by design if so, what steps can I take so that 'by design' means a converted timestamp has a correct UTC Offset?

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

Anonymous — Tue, 18 Jun 2024 10:28:19 GMT

Hi DanielAmbler ,

Apologize for the issue you are facing. The best course of action is to open a support ticket and have our support team take a closer look at it: Link

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

Hope this helps. Please let us know if you have any other queries.

Re: Converted timestamps using expression functions always have UTC timezone - is this expected?

Anonymous — Wed, 19 Jun 2024 09:13:05 GMT

Hi DanielAmbler ,

We haven’t heard from you on the last response and was just checking back to see if you got a chance to open a support ticket.
After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thanks