Problem/Motivation

Pushing a Drupal date field (with time) to a Salesforce datetime field results in an incorrect value stored and displayed in Salesforce.

To reproduce:
1. Create a date (with time) field on a Drupal entity and map this to a Salesforce datetime field.
2. Set data for this field on a Drupal entity and save the entity. Pull up the corresponding object in Salesforce and see that the date is wrong in Salesforce.

It appears that Drupal\salesforce_mapping\SalesforceMappingFieldPluginBase::pushValue() is utilizing strtotime() on the raw value of date fields. These raw values are in UTC timezone, but strtotime() doesn't know this (timezone isn't stored in Drupal dates) and instead converts this value to a timestamp using the default timezone. If the default timezone for the server isn't UTC, then the timestamp strtotime() creates is inaccurate. This timestamp is then converted to a Salesforce-compatible string using the "time zone used to display the page" (see Drupal\Core\Datetime\DateFormatterInterface::format()) and sent off for storage into Salesforce.

It's clear we have some timezone issues in this code. Depending on the server's default timezone and the timezone used to display the page, the time we send to Salesforce can be shifted from what we have in Drupal.

Proposed resolution

Utilize something more robust than strtotime(), something that can convert a UTC Drupal-formatted date into a UTC Salesforce-formatted date (SF also stores dates in UTC), keeping UTC timezone the whole time (maybe Drupal\Core\Datetime\DrupalDateTime).

Remaining tasks

Patch, review.

User interface changes

None.

API changes

None.

Data model changes

None.

Comments

chrisolof created an issue. See original summary.

chrisolof’s picture

Status: Active » Needs review
StatusFileSize
new1.46 KB

Patch attached, needs review.

aaronbauman’s picture

Current approach formats dates using ISO 8601, e.g. 2004-02-12T15:19:21+00:00
This patch proposes eliminating the timezone portion, e.g. 'Y-m-d\TH:i:s'

Will Salesforce assume UTC if no timezone is specified?

cwcorrigan’s picture

StatusFileSize
new685 bytes

Alternative patch attached, it's actually the Date Formatting service that's applying Drupal's default timezone rather than strtotime.

Some brief testing suggests that Salesforce does assume UTC however it's probably better to be explicit and continue to use ISO 8601 rather than rely on undefined behavior as I can't find that explicitly stated in the Salesforce Docs.

aaronbauman’s picture

Version: 8.x-3.x-dev » 8.x-4.x-dev
StatusFileSize
new53.85 KB
new1.43 KB

Looks like strtotime assumes PHP's default timezone.
This causes it to misinterpret Drupal dates, which are all stored in UTC without any explicit timezone attached.

#2 correctly interprets the given date as UTC, whereas #4 incorrectly casts into the wrong timezone.
Here's an example, with a Drupal site whose default timezone is -04:00:
timzones

When formatting for Salesforce, I think we should use the PHP's DateTime::ISO8601 format (equivalent to "c") to be explicit about the timezone.

aaronbauman’s picture

Here's a test to prove this bug exists, and which is solved by #5.

cwcorrigan’s picture

Confirming #5 solves the issue.

aaronbauman’s picture

WTF? Now just have to figure out why the test in #6 fails on my local but passes on testbot.

I hate testbot so much.

aaronbauman’s picture

StatusFileSize
new2.71 KB

Apparently neither assertEquals() nor assertSame() work on even the simplest arrays.
Which means there's literally no way to compare arrays without iterating over every single element recursively?!?!?

Status: Needs review » Needs work

The last submitted patch, 9: salesforce-datetime-push-bug-3074441-TEST-ONLY.patch, failed testing. View results
- codesniffer_fixes.patch Interdiff of automated coding standards fixes only.

aaronbauman’s picture

Status: Needs work » Needs review
StatusFileSize
new4.13 KB

There we go.
And here's the same test + patch from #5

If this passes, i'll push it right away.

aaronbauman’s picture

Status: Needs review » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

armyguyinfl’s picture

I believe this issue 3074441 commit 5783bbe2 line 205-206 broke any SF mappings for any date fields that are mapped. If the value of the date field is null or not populated by user input then you are passing a default current date/time in UTC which SF rejects as a non-valid date (8:00 PM 6/25 = 0000 UTC 6/26 Future Date for GMT -4) for date of birth or invalid future date use cases.

Released: https://www.drupal.org/project/salesforce/releases/8.x-3.5

We had to roll back the Salesforce module to 8.x-3.4 because this broke all lead object submission mappings. This also pollutes any date field that is mapped and null on POST with the current/datetime which is unwanted bad data.

Release notes
Changes since 8.x-3.4:
· #3083942 by AaronBauman, leymannx: Passing URL objects to t-function breaks status page
· Fix test mapping
· Remove debug cruft
· #3078368 by cwcorrigan, AaronBauman: Push Delete doesn't work for Asynchronous Sync
· OAuth updates from 4.x
· Cleaner assertion: don't compare results from ksort, derp
· Add some changes to base auth provider from 4.x
· Adds test coverage around PushParams and SalesforceMapping
· #3074441 by AaronBauman, chrisolof, cwcorrigan: Incorrect time pushed from Drupal date to SF datetime fields

armyguyinfl’s picture

didebru’s picture

First apoligise for hijacking a closed issue.
2 problems with the patch.
DateTime::ISO8601 is not longer available for php >7.2
The date timestamp needs also the ISO8601 format.

Proposal:

       case 'date':
        $this->dateFormatter->format((int) $value, '', 'c');
        break;
      case 'datetime':
        $this->dateFormatter->format((int) $value, '', 'c');
        break;