How to cast DATETIME from UTC to GMT Standard Time on Azure Steam Analytics query?

Vivek Komarla Bhaskar 911

I'm trying to use the below function in an Azure Stream Analytics query to convert DATETIME values from UTC to the London timezone. However, this doesn't work on Azure Stream Analytics, however, it does on Azure dedicated SQL pools.

Works on Synapse Dedicated Pool:

Screenshot 2023-05-15 at 11.15.44 am

Doesn't work on Azure Stream Analytics:

Screenshot 2023-05-15 at 11.18.09 am

PRADEEPCHEEKATLA-MSFT 76,921 Reputation points Microsoft Employee

2023-05-28T14:34:15.49+00:00

@Vivek Komarla Bhaskar - We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

We have provided a detailed answer which has clear steps for which you are looking for. If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Regards,

PRADEEPCHEEKATLA-MSFT

1 answer

PRADEEPCHEEKATLA-MSFT 76,921 Reputation points Microsoft Employee

2023-05-16T05:39:16.5266667+00:00

@Vivek Komarla Bhaskar - Thanks for the question and using MS Q&A platform.

All time handling operations in Azure Stream Analytics are in UTC - Time Management (Azure Stream Analytics). It is recommended that timestamps are in UTC time zone and in ISO 8601 format. Time zone conversion is only possible through JavaScript user-defined functions in Azure Stream Analytics.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Vivek Komarla Bhaskar 911 Reputation points

2023-05-16T06:52:58.9966667+00:00

Hi @PRADEEPCHEEKATLA-MSFT

I have created a Javascript UDF function. As an input to this function, I passed a DateTime in UTC (which is converted from UnixTime using DateAdd) but the output is still in UTC and not Europe/London. Below are the details:

function main(input){ return input.toLocaleString('en-GB', { timeZone: 'Europe/London' }); }

PRADEEPCHEEKATLA-MSFT 76,921 Reputation points Microsoft Employee

2023-05-16T09:36:47.0766667+00:00

@Vivek Komarla Bhaskar - Thanks for sharing the details. We are reaching out to the internal team to get more information related to above issue and will get back to you as soon as we have an update.

PRADEEPCHEEKATLA-MSFT 76,921 Reputation points Microsoft Employee

2023-05-17T05:36:30.44+00:00

@Vivek Komarla Bhaskar - Here is the response from internal ASA team:

Azure Stream Analytics does not support timezones nor DATETIMEOFFSET type. you can just use DATEADD to move the time to specific timezone.

From what I understand currently it is +1 hour, so DATEADD(hour, 1, date).

Hope this helps.

Vivek Komarla Bhaskar 911 Reputation points

2023-05-17T08:02:10.9033333+00:00

@PRADEEPCHEEKATLA-MSFT

Now it is +1hour but after the day light savings are over it will be +0 Hour. This is manual work to do these changes each time, so are you saying this can't be achieved even using Javascript UDF?

Vivek Komarla Bhaskar 911 Reputation points

2023-05-18T09:40:38.03+00:00

Hi @PRADEEPCHEEKATLA-MSFT

Any update, please?

PRADEEPCHEEKATLA-MSFT 76,921 Reputation points Microsoft Employee

2023-05-19T07:04:19.5333333+00:00

@Vivek Komarla Bhaskar - We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Vivek Komarla Bhaskar 911 Reputation points

2023-05-22T08:38:46.63+00:00

@PRADEEPCHEEKATLA-MSFT Any update?

AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2023-05-22T14:56:53.34+00:00

Hi Vivek Komarla Bhaskar ,

Here is the response we got from the team:

"The DATETIME in ASA will always be in UTC, i.e without offset, but it might be possible to use proper conversion in JS function.

One thing looks strange looking at your attempt though. The toLocaleString(...) JS function returns a string, but in the query test results this field appears as type DATETIME, so it seems that ASA performs conversion, basically returning it back to UTC I suspect. Did you define your JS function as taking DAETTIME and returning DATETIME types or taking ANY and returning ANY?

In the later case it should return a string in format like "04/05/2023, 03:53:58".

Vivek Komarla Bhaskar 911 Reputation points

2023-05-22T15:13:41.05+00:00

Hi @AnnuKumari-MSFT Any to Any is what my function does.

Could you share with me what the function would be based on what you said?

Vivek Komarla Bhaskar 911 Reputation points

2023-05-24T09:54:24.55+00:00

Anu update, please?

AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2023-05-26T05:11:12.6066667+00:00

Hi Vivek Komarla Bhaskar ,

First, it is correct that we apparently attempt to interpret the result when the return type is Any. So the fact that we see DATETIME instead of string is expected.

As for converting to specific timezone, the approach is actually correct. toLocaleString is the right function to use.

It does seem however that JS runtime engine thinks that GB London is same as UTC. Using any other timezone works as expected.

May be you can try using some other timezone which has similar changes between date time savings and back.

Kindly accept the answer in case it helped. Thankyou

Vivek Komarla Bhaskar 911 Reputation points

2023-05-30T15:56:12.0133333+00:00

Hi @AnnuKumari-MSFT JS function isn't converting any timezone. It is always resulting UTC. It would be great if you could let me know what needs to be done here to achieve my requirement

PRADEEPCHEEKATLA-MSFT 76,921 Reputation points Microsoft Employee

2023-06-26T04:43:51.7566667+00:00

Hi Vivek Komarla Bhaskar ,

Apologize for the delay in response.

Did you able to resolve the issue or still experiencing the same issue?
Sign in to comment