Clarification about Timezone Conversion in DateTime

Question

There are lots of posts regarding DateTime format. But there are things for which I still can't find a solid answer.

1. I Noticed that Datetime field values are converted into UTC and that converted values are kept in the back-end. When rendering those values in the UI, they will be converted back into the Organization/User's timezone.
2. Is it only the values which are coming directly from the UI (user entered) that are converted or it is any Datetime field values (such as the ones updated through controllers or triggers) that are converted?
3. In a SOQL WHERE clauses, what is the best way to use DateTime fields to avoid potential confusing search results?

I recently noticed this behavior in Salesforce and managed to resolve the difficulties. But I really need to get a clear understanding of the ins-and-outs as a developer :).
Below are the assumptions for each of the aboves that I have taken based on my understanding up until now.

1. Yes.
2. All the fields will be converted. No matter if the value has been set by DateTime.parse(string) or DateTime.valueof(string) or whatever.
3. Hope DATE_TIME_FORMAULA(convertTimeZone(*dateTime*)) = myValue will help here. Until now I've been able to manage with Date fields since there was no need to filter based on time.

UPDATE
When retrieving data via a REST web service does this conversion also happen?

Answer 1

As a baseline Salesforce.com stores all data in the service as UTC.

There are a couple of ways to get to data ..

1. REST API - When you use the REST API Salesforce returns UTC
2. SOAP API- When you use the SOAP API Salesforce returns UTC
3. Standard (OOTB) Page - When you use the standard UI salesforceconverts to the users time zone
4. VisualForce Page - You do the work in Apex Code.

In code you can run in the system context or the user context depending on where you run. If you run in a system context it is always GMT. If you run as a user context you can then use the user timezone in the data you received.

APEX Code Online help for DateTime date() Returns the Date component of a Datetime in the local time zone of the context user.

• dateGMT() Return the Date component of a Datetime in the GMT time zone.
• day() Returns the day-of-month component of a Datetime in the local time zone of the context user.
• dayGmt() Returns the day-of-month component of a Datetime in the GMT time zone.
• dayOfYear() Returns the day-of-year component of a Datetime in the local time zone of the context user.
• dayOfYearGmt() Returns the day-of-year component of a Datetime in the GMT time zone.

There are some tools to help in your SOQL Code ... So you can get the datetime in the API users Timezone. Here is an example from the SOQL Reference

SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount)
FROM Opportunity
GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))