Why DAY_ONLY(convertTimezone()) Query giving wrong results?

Question

I need to display time retrieved from a dynamic SOQL and display at visualforce page using remoteaction. As we know code runs in system context and values in UTC time zone are retrieved. Is there anyway I can retrieve values in running user timezone?

PS: This is complex dymanic query and I can't use converttimezone etc here. Please don't mark duplicate.

Edit: After doing a lot of string manipulation in dynamic query I am getting the results using DAY_ONLY(convertTimezone(CreatedDate)) unfortunately it made things worst. I am getting results including 00:00:00 whereas I should be getting only date:

Status Changed On:2018-07-21 00:00:00

Why time handling is so limited in Salesforce, It would have been great if using with sharing should also do such changes as - timezone, currency etc..

Answer 1

As for database results, you are right, the time is always stored as UTC.

convertTimezone(dateTimeField) would still be your best bet here, in my opinion.

Abou the time component on the date object

When you use System.debug on a Date object you'll get the 00:00:00 right next to the actual date. This is how Apex converts the Date class to String. If you need to send this to the front-end, you can just trim those last characters from the string, or create a new string using .format and passing the Date components (day, month and year).

There are similar issues to this.

About time zone conversion

To display the date in the current user's time zone, you can just use the .date() method of the Datetime class. From the documentation itself:

date()

Returns the Date component of a Datetime in the local time zone of the context user.

So you don't need to query anything or perform any kind of calculation on the date (unless you want to output a different time zone, other than the user's current one).