Search code examples
datedatetimeautomationmicrosoft-graph-apipower-automate

utcNow in Power Automate is using incorrect day (`dd`)

I'm running into a timezone issue when running my Flows on a schedule - and I can't seem to find a solution for it.

My timezone is currently +10:00 from UTC - Sydney, Australia.

For simplicities sake, I have a reoccurrence trigger running at 06:00 (6am local time) and 12:00 (12pm local time).

When I run my Actions - anything that needs a StartTime and an EndTime - I pass in the following parameters:

StartTime Endtime
formatDateTime(utcNow(), 'yyyy-MM-dd"T"22:00:00.0000000Z') formatDateTime( addDays(utcNow(), 1), 'yyyy-MM-dd"T"08:00:00.0000000Z')

The 22:00:00.0000000Z and 08:00:00.0000000Z above translate directly to 8am and 6pm respectively - essentially the entire work day hours.

Issue

When the trigger runs on the first occurrence, the StartTime and the EndTime are outputted to these values:

Tigger time - local UTC date time Start time UTC parameter End time UTC parameter
2022-09-27 06:00 2022-09-26 20:00 2022-09-26T22:00:00.000000Z 2022-09-27T08:00:00.000000Z

When the trigger runs on the second occurrence, the StartTime and the EndTime are outputted to these values:

Tigger time - local UTC date time Start time UTC parameter End time UTC parameter
2022-09-27 12:00 2022-09-27 02:00 2022-09-27T22:00:00.000000Z 2022-09-28T08:00:00.000000Z

Because the UTC date time has ticked over past midnight, the date value (dd) is now the next day due to the addDays(utcNow(), 1) expression.

This causes issues on the scripting of the Flow as when limiting the StartTime and EndTime of other actions, depending on when the Trigger runs is either looking at the local time's "Today" events or "Tomorrow" events.

For example, if I schedule an Out of Office / Automatic reply - and I schedule it to run on the 06:00 trigger, it will set my Automatic Reply to today - local time - 8am to 6pm. However, when it runs at the second trigger, 12:00 it will set my Automatic Reply to tomorrow - local time - 8am to 6pm.

Though this is not how I am using it, you can see it affects what actions do and perform.

Is there a way to ensure that it is always working of the current local date (dd) regardless if the UTC time has ticked over?

Solution

  • Though the other solutions seem logical in their workings, they dont account for daylight savings and other date time issues.

    For the solution I ended up hard coding some of the data and calculations:

    1. Action: "Convert time zone"

      1. Base time: trigger time
      2. Format string: Universal sortable date/time pattern [u]
      3. Source: +10:00
      4. Destination: UTC

    2. Action: "Compose"

      1. Expression: formatDateTime( body('Convert_time_zone'), 'dd')

    Then for the start time UTC:

    1. Action: Initialise variable

      1. Name: Output Start Date Time
      2. Type: String
      3. Value: yyyy-MM-[Output from Compose*]"T"22:00:00.0000000Z')

      * in that [Output from Compose] I made it an expression so I could subtract 1 day, using: sub( int(outputs('Compose')), 1)

    This way I could always get my local date (dd) then manually shift the date, and set it as well as the time for start time.

    Then I would use that local date for the End date time, and manually set the time.

    Not the cleanest solution, but it works 100% of the time regardless of daylight savings or any time conversions. It is however not transferrable to other users without them editing the info.