cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeeTronSEAM
Advocate V
Advocate V

Dealing with timezones for date values in Flow (SQL insert changed to SQL Server timezone. UTC <> GMT)

TL;DR;

  • Don’t use SQL Server DATETIME datatypes if you can use DATETIMEOFFSET instead.
  • If using SQL Server DATETIME datatypes, watch out for Flow/SQL Connector unexpectedly changing a Z-stamped (i.e. UTC) ISO 8601 date values to the equivalent date for the SQL Servers system timezone (as configured in the Windows Server Dates & Times control panel).
  • GMT is NOT the same at UTC.
  • “Standard Time” timezones are not “Standard Time” during Daylight Savings Time periods.
  • The covertFromUtc function WILL take care of converting to DST based on the date of the input date despite the target timezone being designated as an “XXX Standard Time” Microsoft Time Zone Index Value (see ref. link below).

 

Overview:

 

Recently I discovered an issue with Flow changing the value of a utcNow() datetime expression when inserted into an on-premises data gateway SQL Server using the SQL Server connection.  We’ve opened that as a bug with the Power Automate team but I thought I’d mention the observation while we wait for a response from Microsoft Support.  Also, while troubleshooting the issue, I learned of some unexpected behavior from the Power Automate/Logic App functions convertFromUtc and convertToUtc.  I thought I’d also share those observation in case it helps others.

 

 

First, for the SQL DB insertion issue:

 

We have a Power Automate flow that creates records in two SQL Servers whenever a SharePoint list item is created or updated.  Both SQL Servers should contain identical information as inserted from two SQL Insert Row actions.  Instead, a DateTime column named RowLastModifiedUTC ends up with the correct value in one database, but a timezone adjusted value in the other. 

 

Both values for the two RowLastModifiedUTC columns come from a simple utcNow() function and both show the expected value in the flow run results GUI. But, the outputs of the SQL action show the differing values that are ending up in the database.

 

It seems the Flow is determining the system time of each SQL server and undesirably changing the insert value for the SQL Server that is on Mountain Time (-7 GMT) but leaving it as UTC (as is expected/desired) for the SQL Server with a UTC (0 GMT) system time.  We’ve tried to force the output of the function to be an explicit UTC datetime value but the Flow still ignores this and still alters the value to account for the target SQL Server’s system time. 

 

In short, the input parameter ( as generated by the utcNow() function) change from

    "item/RowLastModifiedDateUTC": "2021-02-16T17:53:16.6984530Z",

to

         "RowLastModifiedDateUTC": "2021-02-16T10:53:16.7Z"

 

 

 

Furthermore:

While trying to determine what we might do to modify our systems with to address how the SQL insertions are getting unexpectedly altered, we attempted to “override” the implicit timezone conversion the SQL connector seems to be doing to our UTC datetime values, I discovered that our assumptions about the terms “UTC,” “GMT,” “GMT Standard Time,” and “Greenwich Standard Time” were incorrect.  In digging into that we discovered why Microsoft provides both a “GMT Standard Time” AND a “Greenwich Standard Time” Microsoft Time Zone Index Value enumerations, but no “UTC”  option. Previously to the tests provided below, the mistaken assumptions were:

  1. that GMT = UTC (it does NOT!),
  2. and that “GMT Standard Time” and “Greenwich Standard Time” were synonyms (they are NOT!)
  3. and that the Logic Apps’ timezone converting functions (e.g. convertFromUtc() ) would NOT account for Daylight Savings Time for applicable input dates (they DO!).

 

The test expression we used (in a Compose action) follow. 

I hope this helps others who may also be mistakenly making assumptions about these datetime conversion issues.

 

 

 

concat(

    '|Use convertFromUtc Jan date 2021-01-01T10:01:01.111Z to GMT Standard Time = '

    , convertFromUtc('2021-01-01T10:01:01.111Z', 'GMT Standard Time')

    , '|Use convertToUtc Jan date 2021-01-01T10:01:01.111 from GMT Standard Time = '

    , convertToUtc('2021-01-01T10:01:01.111', 'GMT Standard Time')

    , '|Use convertFromUtc Jan date 2021-01-01T10:01:01.111Z to Greenwich Standard Time = '

    , convertFromUtc('2021-01-01T10:01:01.111Z', 'Greenwich Standard Time')

    , '|Use convertFromUtc Jan date 2021-01-01T10:01:01.111Z to Mountain Standard Time = '

    , convertFromUtc('2021-01-01T10:01:01.111Z', 'Mountain Standard Time')

    , '|Use convertFromUtc Jul date 2021-07-01T10:07:01.777Z to GMT Standard Time = '

    , convertFromUtc('2021-07-01T10:07:01.777Z', 'GMT Standard Time')

    , '|Use convertToUtc Jul date 2021-07-01T10:07:01.777 from GMT Standard Time = '

    , convertToUtc('2021-07-01T10:07:01.777', 'GMT Standard Time')

    , '|Use convertFromUtc Jul date 2021-07-01T10:07:01.777Z to Greenwich Standard Time = '

    , convertFromUtc('2021-07-01T10:07:01.777Z', 'Greenwich Standard Time')

    , '|Use convertFromUtc Jul date 2021-07-01T10:07:01.777Z to Mountain Standard Time = '

    , convertFromUtc('2021-07-01T10:07:01.777Z', 'Mountain Standard Time')

)

 

And the results/conclusions:

// Note that a Z-stamped UTC date to GMT during standard time is unchanged which is correct, but see below for a daylight savings time date.

        Expression:

               convertFromUtc('2021-01-01T10:01:01.111Z', 'GMT Standard Time')

        Results:                        2021-01-01T10:01:01.1110000

 

// Note that using the convertToUtc date throws an error if Z-stamped, so we removed it but then did get the correct unchanged time, but see below for a daylight savings time date.

        Expression:

               convertToUtc('2021-01-01T10:01:01.111', 'GMT Standard Time')

        Results:                   2021-01-01T10:01:01.1110000Z

 

// Back to using the convertFromUtc function but with "Greenwich Standard Time" as the target TZ, we also get the correct value.

        Expression:

               convertFromUtc('2021-01-01T10:01:01.111Z', 'Greenwich Standard Time')

        Results:                        2021-01-01T10:01:01.1110000

 

// Finally (for a date in standard time), converting from a Z-stamped date to Mountain Standard Time, we get the correct value (i.e. UTC-7).

        Expression:

               convertFromUtc('2021-01-01T10:01:01.111Z', 'Mountain Standard Time')

        Results:                        2021-01-01T03:01:01.1110000

 

 

//Now let's try a date that occurs during Daylight Savings Time...

 

// This time using a July date and coverting from a Z-stamped date with convertFromUtc to "GMT Standard Time" we get an unexpected value.

               // The value may be accurate, but may not be expected if you (mistakenly?) think "UTC" and "GMT Standard Time" are the same.  Not so.

        Expression:

               convertFromUtc('2021-07-01T10:07:01.777Z', 'GMT Standard Time')

        Results:                        2021-07-01T11:07:01.7770000

 

// Once again, for this test using convertToUtc, we have to remove the Z-stamp to avoid the error, but we also get an unexpected value.

               // This is really the reverse of the test right above.  I.e. "GMT Standard Time" does shifting for DST and is thus NOT the same as UTC (or Greenwich Standard Time" as we'll see below).

        Expression:

               convertToUtc('2021-07-01T10:07:01.777', 'GMT Standard Time')

        Results:                   2021-07-01T09:07:01.7770000Z

 

// Back to using the convertFromUtc function but with "Greenwich Standard Time", we DO get the correct and expected value. I.e. "Greenwhich Standard Time" = UTC

        Expression:

               convertFromUtc('2021-07-01T10:07:01.777Z', 'Greenwich Standard Time')

        Results:                        2021-07-01T10:07:01.7770000

 

// Finally, converting from a Z-stamped date (which is during DST!) to Mountain Standard Time, we get the correct value (i.e. UTC-6).

        Expression:

               convertFromUtc('2021-07-01T10:07:01.777Z', 'Mountain Standard Time')

        Results:                        2021-07-01T04:07:01.7770000

 

 

 

 

Reference Links:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#c...

 

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#c...

 

https://docs.microsoft.com/en-us/previous-versions/windows/embedded/ms912391(v=winembedded.11)

 

https://en.wikipedia.org/wiki/Greenwich_Mean_Time

 

https://en.wikipedia.org/wiki/Coordinated_Universal_Time

 

https://en.wikipedia.org/wiki/Daylight_saving_time

 

 

 

0 REPLIES 0

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (1,840)