+ Start a Discussion
KrIsH ChKrIsH 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  Lead Age formula
ApuroopApuroop
Salesforce stores a DateTime field in UTC and displays the field value in the current user's time zone or company's default time zone.

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
KrIsH ChKrIsH Ch
Thanks for your input Apuroor. I checked the link that you sent but how do I make sure that I get same results when I use SOQL query ?
 
ApuroopApuroop
I don't think we can handle the conversion when querying via SOQL. However when you get the .csv file of all these records, you can convert the datetime value to your current time zone in Excel and create a FORMULA on the latest datetime values based on the Salesforce formula. Hope it makes sense.
KrIsH ChKrIsH Ch
Sorry to say this but that didn't really help me resolve my issue. Thanks though for your reply here
ApuroopApuroop
No worries.

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.
IF( NOT(ISBLANK(Lead_Created_Date__c)), (NOW() - Lead_Created_Date__c), 0 )

FormulaForSOQL is the one that you would use for querying.in the Developer Console. 
IF( NOT(ISBLANK(Lead_Created_Date__c)), ((NOW() - Lead_Created_Date__c) - (N/24)), 0 )
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
KrIsH ChKrIsH Ch
First of all sorry for the delay and Thanks Anuroop for your response. I like the way how you are approaching to this issue. Currently, I am I am in PST and we also have business users from CST and EST. Sometimes they create records so how should I go with this kind of situation?.
ApuroopApuroop
Guess I got another idea, lol!

Create another formula, let's say Current Time Zone (Number). Source: https://success.salesforce.com/answers?id=9063A000000suLPQAY
( (DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 02:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 03:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 04:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 05:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 06:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 07:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 08:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 09:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 10:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 11:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 12:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 13:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 14:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 15:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 16:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 17:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 18:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 19:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 20:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 21:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 22:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 23:00:00")) - TODAY()) ) +1
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 + :
IF( NOT(ISBLANK(Lead_Created_Date__c)), ((NOW() - Lead_Created_Date__c) + (Current_Time_Zone/24)), 0 )

I didn't perform a proper testing but this should get you started. Hope it helps!
KrIsH ChKrIsH Ch
Anuroop,

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
 
ApuroopApuroop
Salesforce doesn't convert the timezone during daylight savings. Test it yourself, create an account and check the createdDate's time against your system's. It'll be an hour behind. 

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?
KrIsH ChKrIsH Ch
Sorry Apuroop this is not helping me but thanks for all your responses