I'm trying to get the date and time of a Message record in the user's timezone and date/time formats. ✅ Date format - Seems to be automatic ✅ Time format - I am getting the format from the user's preferences and reformatting it to Oracle SQL TO_CHAR() format ❌ Time zone - The hour doesn't match the hour of the time on the actual record I'm running this in my console on the Message record itself, so it should be using the same role for running the SuiteQL and viewing the record. I'm also using this same SuiteQL for other record types such as User Notes, Files, and a custom record, and they all return in the correct time zone. Is there a better way to get the date and time in the user's time zone and format? In my example below, the time in the UI says 7:10 am.

SELECT
  id,
  datetime || ' ' || TO_CHAR(datetime, 'fmHH:MI am') datetime, /* returns 3:10 pm */
  messagedate || ' ' || TO_CHAR(messagedate, 'fmHH:MI am') messagedate, /* returns 12:10 pm */
  lastmodifieddate || ' ' || TO_CHAR(lastmodifieddate, 'fmHH:MI am') lastmodifieddate /* returns 7:10 am, what I ended up using */
FROM Message
WHERE id = 269550

Edit: I figured out that if I use lastmodifieddate, it returns the correct time of 7:10 am. Why are there 2 other date fields that are in 2 different time zones?

This is how you get the time as UTC, not sure if it helps, but it might get you closer. You might then use JavaScript (e.g. moment.js) or Python/Ruby/etc. to convert the UTC timestamp to the users's timezone

SELECT to_char(sys_extract_utc(transaction.lastmodifieddate), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS transaction_lastmodifieddate FROM transaction