+ Start a Discussion
ltcommanderltcommander 

SOQL Timezone question

Hi guys,

I see so much info on this but I am still unable to figure it out!

Our SF set up is Hong Kong. So, all times (LastModifiedDate, etc) are saved and displayed on SF as per the HK timezone..

I am trying to use SOQL to retrieve data.

my $result = $sforce->do_query("Select LastModifiedDate,Id,Name From Job__c where LastModifiedDate>=2018-01-17T00:00:00.000Z and LastModifiedDate<=2018-01-17T23:59:00.000Z" ); 

Does the above mean 17th Jan from 00:00 to 2359 UTC?

HK is in the +8 timezone. So if I want to see the records created during the day of 17th Jan as per the HK timezone how would I modify the DateTime part? Supposed to be easy but I am still struggling with it!

Thanks!



 
Raj VakatiRaj Vakati
ALways your SOQL queries will run based on the User Time zone. Date Time adjustment will be taken care by salesforce at runtime.
 
ltcommanderltcommander
THank you Raj. So, depite the "000Z" this query: 

my $result = $sforce->do_query("Select LastModifiedDate,Id,Name From Job__c where LastModifiedDate>=2018-01-17T00:00:00.000Z and LastModifiedDate<=2018-01-17T23:59:00.000Z" ); 

will only bring up records created on the 17th (Hong Kong timezone) ?

Thank you
 
Raj VakatiRaj Vakati
I am sorry for wong update. 
dateTime field values are stored as Coordinated Universal Time (UTC). When a dateTime value is returned in Salesforce, it’s adjusted for the time zone specified in your org preferences. SOQL queries, however, return dateTime field values as UTC values. If you want to process these values in different time zones, your application might need to handle the conversion.

Refer this link 
 
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_convert_time_zone.htm


 
Raj VakatiRaj Vakati
https://force746.wordpress.com/2013/09/04/gotcha-converttimezone-must-be-used-in-soql-date-functions-dealing-with-datetime/
Karthikeyan MBKarthikeyan MB
Got a similar situation and my observation
Select id from lead where createddate = TODAY
observation: returns results based on the logged in user timze zone. So the count of records from this query will vary based on the timezone of the user

So instead of using the date literal 'Today' you can try using the date format in the query
Select id from lead where createddate = 2018-11-27T05:00:00.000z
observation: returns results based on the defined date. The date here refers to the UTC date. It does not take logged in user time zone or the org timezone. The count remains same irrespective of logged in user timezone.

If the users are spread across timezones (e.g. EST, MST, CST, PST) and if you need records created based on EST timezone (instead of UTC 00:00), you can adjust the above query to
Select id from lead where createddate = 2018-11-27T05:00:00-05:00.  The last part "-05:00" is the offset that needs to be applied based on UTC base timezone. Note: The offset needs to be adjusted based on DLS