2

In Apex, I would like to get the cases created in January 2016 based on organization time zone. E.g. Organization time zone is GMT+8 and a case record has been created in 31/12/15 23:00:00 GMT , Is there any elegant solution to retrieve this record by a query?

Moreover, is this query should do the job? I don't feel so because the convertTimeZone is done based on the context user timezone and not organization

select ID from case where CALENDAR_MONTH(convertTimezone(createddate)) = 1

Thanks ahead. :)

1

You'll want to take into consideration that all data held in the database is stored in GMT or UTC. It's only when it's returned that it's converted for display in the User's Local Time Zone. So without knowing the context in which your query will be run via Apex, I'm not certain anyone can answer your question.

If the Apex is run in a system context, you'll get one answer. If it's run in the context of a specific user, you'll get a different answer. Without telling us how you plan to execute the Apex code, no one can answer your question with any degree of certainty. If your desire is to run some kind of report by a user who's located in in the same location as where your Organization is headquartered, then you'd have something more specific that you'd be able to get results from. All you'd need to do then, is run the query from that specific location and you'd obtain the results you're looking for.

If you run the query from some other location, then you'll want to calculate the offset between that timezone and the timezone of your Org. Why? Because what's returned at that location will be returned in it's timezone, not the Org's timezone and you'll want to convert those results to the local time of your Organization. It's the offset between the two timezones that will be what's of importance to you. There's a Timezone Class that has methods that can help you with those calculations.

With that having been said, I recommend you look at Date-Time methods as there is no method called convertTimezone (there's only getTimezone() or getOffset() and a few others).

The things I'm familiar with that would seem to be helpful would be format(DateFormatString, timezone) and formatGMT(DateFormatString, timezone). You can also parse(Date-TimeString) to get a Date-Time in your localtimezone or use something like valueOf(fieldValue). What you'll actually need to do will depend on your specific implementation. Be sure to look at the Date methods too as they may be appropriate as well. There are numerous ways of doing this, all of which are dependant on how you decide to implement this.

  • Thank you for you answer. As for convertTimezone, please see URL below: developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… Moreover, You mentioned that there is a difference between System Mode and User Mode, and thus Apex will provide different results. Could you please provide an example for that? Thanks. – sfdcdev Jan 10 '16 at 15:38
  • 1
    System mode refers to who the running user is. If the code is running as say an API user. It wouldn't have the context of a local user. It all depends on where the code is called from, a visual force page vs. a trigger, a batch class, etc and whether with sharing or without sharing. I can't be more specific than that. – crmprogdev Jan 10 '16 at 15:43
  • Ok I will search for the differences. Back to my question, So Salesforce doesn't provide the way of knowing the Org time zone in run time, right? – sfdcdev Jan 10 '16 at 15:50
  • If the code is running in the context of a particular user, especially if running from a visualforce page, yes you can get the user's timezone from their User Info. If the code is running from say a trigger, it's not as likely to be as readily available because you don't have the same context. The records, for example, could have been entered via dataloader. – crmprogdev Jan 10 '16 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.