Salesforce : Date filter on TODAY/YESTERDAY

  • 62Views
  • Last Post 27 April 2018
0
votes
vpeigneux posted this 11 April 2018

Hello,

I have a Salesforce query on Contact with filters on LastModifiedDate <= TODAY AND LastModifiedDate >= YESTERDAY (see attached screenshot). The package is scheduled at 00:15 every day (UTC+01:00).
The problem is that I get contacts from D-2 and D-1 : today (11/04) I got contacts modified on 09/04 and on 10/04. I only want contacts modified on D-1.

Is there a problem with the hour of the schedule so the package is executed the day before ?

Attached Files

Order By: Standard | Newest | Votes
0
votes
Simon Bubnov posted this 13 April 2018

We have reproduced the issue. The reason of the issue is the following. Salesforce assigns and displays the record creation time considering timezone. Skyvia queries data from Salesforce in UTC, so all the date/time values are converted to UTC. However, the relative constants are calculated in the local timezone, but are applied to datetime values in UTC. 

 

For example, you add a record at 4/13/2018 12:20 AM in your timezone, and this time will be accordingly displayed in Saleforce. However, in UTC this value is equal to 4/12/2018 11:20 PM. Skyvia processes Salesforce date and time values in the UTC. Thus, all the records, created today between 12:00 AM and 1:00 AM in your timezone are considered as created on the previous day in UTC.

 

We will investigate the possibility to process date values in timezone different from UTC and post here about the results.

0
votes
vpeigneux posted this 25 April 2018

Hello,

 

Do you have a workaround while waiting for the fix ?
Like adding one or two hours on the schedule of the package or put the timezone on UTC+0 ?

0
votes
Simon Bubnov posted this 26 April 2018

There is one workaround, but it requires some manual work and using Skyvia Query tool (it allows executing up to 5 queries per day for the Free subscription).

1. Switch to the Query tab.

2. Click New SQL.

3. Select your Salesforce connection.

4. Enter the following query:

 

SELECT t.*

 FROM Contact AS t

 WHERE (date(t.LastModifiedDate, '+1 hour') >= date('now', 'localtime', '-1 day') AND date(t.LastModifiedDate,'+1 hour') <= date('now', 'localtime'))

 

5. Click the Execute button, and this query will return all data from the Contact object, for which LastModifiedDate is between yesterday and today (considering timezone UTC+1). Then click the CSV button in the Result header to export CSV file: http://prntscr.com/jadnvl

6. Now you can create an import package from CSV to your destination. The CSV file will contain filtered data that you want, and you do not need to add any filter for this package.

 

Skyvia Query does not support scheduling, so you need to execute the query and export the result to CSV manually.

0
votes
vpeigneux posted this 26 April 2018

It seems to be a manual process. The goal for us is to export contacts automatically every night.

0
votes
Simon Bubnov posted this 27 April 2018

Yes, it is a manual process. There is no workaround for scheduling process, and you need to wait for the fix.

Close