Compare Dates With RForcecom

November 25, 2016
By

(This article was first published on Dan Thompson, and kindly contributed to R-bloggers)

Have you used RForcecom to connect R to Salesforce? It’s really cool, and thanks to it using Bulk API, it’s absolutely rapid.

Anyway, I recently came across a problem when trying to query records which had been edited based on the timestamp.

I wanted to do something like this:

time_now<-Sys.time()
q <- paste0(“select Id from opportunity where LastModifiedDate > “,time_now)
updated_ids<-rforcecom.query(session,q);

However, the Salesforce time format does not appear to match any of the POSIXlt or POSIXct formats in R (please correct me if I’m wrong!).The problem appears to be that R datetimes are generally similar to this:

“2016-11-12 12:15:32 GMT”

whereas the Salesforce format has a “T” (between the date and time) and a “Z” (after the time) in there for good measure.

“2016-11-12T12:15:32Z”

I spent a while trying to do an R conversion, then after some minor irritation just did my own hack function which appears to work:

convertToSalesforceTime<-function(mytime)
{
output<-paste0(as.Date(mytime),”T”,strftime(mytime,format=’%H:%M:%S’),”Z”)
return(output)
}

Now, if you do the code in full as below, you can query using RForcecom and only select records within certain time periods!

# Assume already logged in to the API…
# run the function
convertToSalesforceTime<-function(mytime)
{
output<-paste0(as.Date(mytime),”T”,strftime(mytime,format=’%H:%M:%S’),”Z”)
return(output)
}
# get the time you want to query
time_now<-Sys.time()
# convert it so Salesforce is happy
sf_time_now<-convertToSalesforceTime(time_now)
# query Salesforce
q <- paste0(“select Id from opportunity where LastModifiedDate > “,time_now)
updated_ids<-rforcecom.query(session,q)

I’m sure there’s a real way around this but I couldn’t find it on Google, so this may prove helpful to someone.

Also, I believe the T and Z are something to do with time zone, so please take extra care when working across multiple timezones.

To leave a comment for the author, please follow the link and comment on their blog: Dan Thompson.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)