2

Here is the problem. We use Stripe to process credit card payments, and Stripe deposits the money into our account two days later, minus fees. I've created a schedulable class that runs every morning, and creates the deposits and fee payments automatically in our SalesForce Accounting Application. This way, our org sees the money appear in real time in our accounting software at the same time we get the deposit from Stripe. However, some of the payments are not pulled in the query, so I think that we have a problem with timezone conversion. We are in NYC, GMT-5, and payments after 7pm EST are not pulled.

Here is my Query:

//Query Payments with a date of today - 2 days date paymentdate = date.today().addDays(-2);

    List <Payment__c> payments = [SELECT name, id, Amount__c, Payment_Date__c FROM  Payment__c WHERE DAY_ONLY(convertTimezone(Payment_Date__c)) = :paymentdate AND Payment_Processor__c = 'Stripe'];

In this case paymentdate is a Date field, which should be in EST, and Payment_Date__c is a DateTime field, which is a timestamp for the payment's receipt, should be in GMT. I've seen the posting here: Using Date in SOQL to get only records created on a certain date which addresses this issue, however my code is written like the solution, so I'm not sure how to fix this. Any advice would be appreciated.

0

2 Answers 2

0

I second opinion expressed by NSJonas above

Paymentdate will be in EST, provided user who has scheduled the job is also in EST time zone. Please verify that first. Seems like GMT date is converted into EST but is compared with some date in some other timeZone

I did a simple test : created a new scheduled Job with just debugging statements like this :

global class DateTimeZoneCheck implements Schedulable {
   global void execute(SchedulableContext SC) {
    system.debug(date.today());
    system.debug(system.now());

   }
}

Date.today() was printed in debug log , in time zone of user who has scheduled the job while System. now was in GMT like this :

enter image description here

1
  • You are also correct, test came back and user timezones were correctly configured. It was a problem with the Stripe timezone settings. See above.
    – Jason Sage
    Commented Sep 8, 2015 at 16:08
0

convertTimezone() changes the DateTime to the timezone of the executing user. It's possible that the executing users timezone is not set to EST. For the scheduled task, I believe this would be the user who actually scheduled the task.

To make debugging easier, you could run this query in the developer console (as the executing user) and see what is returned.

SELECT name, id, Amount__c, Payment_Date__c, 
convertTimezone(Payment_Date__c) 
FROM  Payment__c 
WHERE DAY_ONLY(convertTimezone(Payment_Date__c)) = :paymentdate 
AND Payment_Processor__c = 'Stripe'

I added convertTimezone(Payment_Date__c) to the select clause so you can easily compare how the times is getting converted.

2
  • Thanks for your efforts here. The executing user is an API user set to EST. The webpayment page also uses a guest user whose timezone is set to EST. We did it this way so that there wouldn't be a situation where the executing user and payments guest user would always be in the same timezone. I've done some debugging here, and it turns out that the original code works. The problem turned out to be with the timezone settings with Stripe itself.
    – Jason Sage
    Commented Sep 8, 2015 at 15:58
  • Our Stripe timezone settings were set to EST. When we ran reports, we had EST dates on the reports, but the API was returning UTC dates to SFDC. This is the cause for the error. The fix is to convert the Stripe UTC date to EST/EDT within the API call.
    – Jason Sage
    Commented Sep 8, 2015 at 16:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .