You need to sign in to do that
Don't have an account?
KrIsH Ch
TimeZone issue between Salesforce UI and queried results from developer console
Hi Everyone,
I have created a "lead age" formula field in Salesforce to calculate the age from the time its created. When I query few records from developer
console it's giving me a different value than what I see on salesforce UI.
Ex: For certain records, the lead age on UI is showing as 1 and when I query the same record from console then it is giving me the result as 2.
I know it is a timeZone issue but I don't know how to fix this issue.
Can someone help me resolve this issue?. Your help on this is really appreciated
I have created a "lead age" formula field in Salesforce to calculate the age from the time its created. When I query few records from developer
console it's giving me a different value than what I see on salesforce UI.
Ex: For certain records, the lead age on UI is showing as 1 and when I query the same record from console then it is giving me the result as 2.
I know it is a timeZone issue but I don't know how to fix this issue.
Can someone help me resolve this issue?. Your help on this is really appreciated
The reason you're getting a different value is because the formula is calculating on different sets of date values.
Here's something which is helpful:
https://salesforce.stackexchange.com/questions/26951/is-there-a-default-timezone-in-which-date-fields-are-stored-in-salesforce
Here's an idea, try it out..
You will have two formula fields, FormulaForUI and FormulaForSOQL:
FormulaForUI is the one that you posted above. I reckon that's working as per your requirements. I used NOW() to get an even more precise timestamp.
FormulaForSOQL is the one that you would use for querying.in the Developer Console. If you are in Eastern Standard Time then N = 3 + 1(Daylight savings).
If you are in Central Standard Time then N = 4 + 1(Daylight savings).
If you are ahead of GMT, then change the operator to addition(+) and add the appropriate hours. In this way, we are handling the conversion ourselves.
The first formula would be visible in the page layout whereas the second one isn't. I'm not saying this is perfect but it should put you in the right direction. Hope it helps.
Since you can't directly convert the timezone in a formula, we are going for this approach. Found it here: https://success.salesforce.com/answers?id=90630000000D4gIAAS
Create another formula, let's say Current Time Zone (Number). Source: https://success.salesforce.com/answers?id=9063A000000suLPQAY This will return the difference of the GMT and the user's current timezone. Note that I added an hour because of the Daylight Savings. You need to figure out a way to indicate that the "TODAY()" falls under DLS or not. DLS starts on the first sunday of March and ends on first sunday of November. Other way is you need to change the formula manually, by creating a reminder task in salesforce for yourself.
You need to reference this field in your FormulaForSOQL like this, I changed the math operator to + :
I didn't perform a proper testing but this should get you started. Hope it helps!
As you mentioned I created a formula field Current_Time_Zone__c and I also updated Lead Age with (Current_Time_Zone/24) so I look at a sample record result shows as follows
UI:
Lead Created Date : 8/17/2019 12:44 AM
Current Time Zone: -6.00
Lead Age: 4
SOQL results:
Lead Created Date : 2019-08-17T07:44:19.000+0000
Current Time Zone: -7.00
Lead Age: 4
So there is still an issue with this timezone thing. Can you suggest what's going wrong?
Thanks again for your time on this issue
I just queried the CurrentTimeZone in SOQL and am getting the same result as I see in UI.
That being said, temporarily change all those number formulas' decimal places to 2 or more, this will give you a clear picture on how the system is rounding up the numbers. Other than the timeZone, everything else is working as expected, correct?