[SalesForce] How to Address Daylight Savings Complications when Converting Date to Date/Time

Note: I've tagged this question with 'Apex' because there doesn't seem to be any other way to resolve it. I'm able to call on an Apex Action in a flow, but I need help with the code.

Background

My org's timezone is GMT+10 or GMT+11, depending on whether or not daylight savings is in effect.

I have a flow which takes a date (StartDate) and converts it to a date/time value. I need the date/time value to be 9:30am on the same day as the date value.

As you know, Salesforce stores dates in GMT. So if the date value is 1/10/2019, the DATETIMEVALUE(StartDate) resolves to 1/10/2019 10am.

Which means DATETIMEVALUE(StartDate) – 0.5/24 resolves to 1/10/2019 9:30am – which is the date and time that I need.

Issue

The problem is, due to daylight savings, if the date is 1/11/2019, the same formula results to 1/11/2019 10:30am.

Question

How can I ensure that the date/time value derived from the date field (StartDate) is always the same date at 9:30am?

I'm starting to think this can only be achieved via Apex. This should be okay as I'm able to call on an Apex Action from a flow.

Things I've Tried

  1. Using this formula: DATETIMEVALUE(TEXT(YEAR(StartDate)) & "-" & TEXT(MONTH(StartDate)) & "-" & TEXT(DAY(StartDate)) & " 09:30:00")

  2. Using this formula with Today()

  3. Using this formula with StartDate

Any help would be much appreciated.

Best Answer

Well, let's say a particular time zone is the one you want the "9:30 AM" of. For argument's sake let's make it Australia/Sydney (full list here: https://www.australia.gov.au/about-australia/facts-and-figures/time-zones-and-daylight-saving).

// assume a Date object inputDate exists already
Timezone tz = Timezone.getTimeZone('Australia/Sydney');
Integer offset = tz.getOffset(inputDate);
Time t = Time.newInstance(9, 30, 0, 0);
Datetime dt = newInstanceGmt(inputDate, t).addSeconds(-offset/60);

I may or may not have made an error at this hour, but the idea is:

  • Get a reference to the time zone in question
  • Get the offset for it on the day of the input date - note that in North American time zones I usually need to use inputdate+1 here because at midnight GMT we haven't changed time zones yet. I think for UTC+10/11 you would have already shifted by midnight GMT though.
  • Create a Time instance (which is zone-free) and create a GMT DateTime matching the input day and time
  • And finally, shift it the opposite direction of the offset, so that you get it in the right zone.
Related Topic