Timezone Azure Data Factory - London Location

Question

Hi Team,

London usually follows two timezones during the year, GMT & BST (GMT is UTC+0, BST is UTC+1). I'm trying to insert a datetime value to a table on my synapse and I want to use a function in dataflow expression builder which can handle this scenario automatically instead I manually adding +1 during day light saving and remove +1 when day light savings is over. Is there anything as such, please? Right now I'm using the function - currentUTC('GMT-1') during daylight savings and modifying it back to currentUTC('GMT') when day light savings are over.
I really don't want to do this manual work every year.

Answer 1

Hello @Vivek Komarla Bhaskar ,

Thanks for the follow up query and sorry for the delay in getting back. I was working with relevant product team to get clarifications regarding the behavior of datetime conversion functions in mapping dataflow based on the region of the data factory.

As per my analysis, below are observations:

Case 1:

As per this statement from original query: Right now I'm using the function - currentUTC('GMT-1') during daylight savings and modifying it back to currentUTC('GMT') when day light savings are over - I understand that you are deducting 1 hour from GMT during day light saving and just taking GMT after Day light saving ends to make sure that you use only the Standard time irrespective of Day light saving. Please correct If my understanding is not clear.

If you want to maintain the standard time zone, you don't have to subtract 1 hour from GMT as GMT and UTC display same time (Standard Time). (Please see below expression and its result for clarification)

For this you can use these expressions:

currentUTC('GMT')  
  
Or   
  
currentUTC()  

Case 2:
In case if you want to use the current London time then you will have to use below function to get the current London time. I believe this is what you are looking for. Please let me know otherwise.

fromUTC(currentUTC(), 'Europe/London')  

You can play around with this below expression which give understanding of different time zone behaviors in Mapping dataflow expression:

' CurrentLocalTime = ' + toString(currentTimestamp()) + ' , GMT = ' + toString(currentUTC('GMT')) + ' , UTC = ' + toString(currentUTC()) + ' , London = ' +  toString(fromUTC(currentUTC(), 'Europe/London'))   

For your requirement, if you want to save startDateTime into Synapse table and if you expect the current local time in London to be logged for that column, then please use fromUTC(currentUTC(), 'Europe/London') and in case if you want to log standard time irrespective of Day light saving then you can use currentUTC() or currentUTC('GMT')

Please Note:

• fromUTC(currentUTC(), 'Europe/London') > currentUTC('GMT') by 1 hour during Daylight Saving Time
• fromUTC(currentUTC(), 'Europe/London') = currentUTC('GMT') after Daylight Saving Ends

Hope this clarification helps. Please let me know if you have further questions or concerns regarding this.

We look forward to your confirmation.

Thank you

Answer 2

Hello @Vivek Komarla Bhaskar ,

Thanks for the question and using MS Q&A platform.

As per my understanding, you have a requirement where you need to dynamically(automatically) determine if it is GMT or BST timezone and then calculate the datetime value and insert it into a table. Right now, you are doing it manually and wanted to automate it. Please correct me if I'm not clear.

As per my analysis the best way is to use the expression builder in dataflow and use Case function and within it write a condition to get the current time in UTC and compare it to the current time in Europe/London (nothing but your local time) and calculate the difference. If they differ (nothing but > 0), then BST is in effect and use the value of UTC+1. Else they're identical, just use the value of UTC.

Hope the above approach helps to avoid your manual intervention. Please let us know if you further need assistance or if you have any questions regarding the same.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how