Convert DATE in SQL (Oracle) from UTC to Brazilian time zone

Question

I have the following query:

SELECT c.name, t.sequential_id AS sequencialid, 
       CONCAT(SUBSTR (REPLACE (m.content, '¿', ''), 0, 30), '...') AS content, 
       m.direction, 
       (CASE WHEN TRUNC("M"."DATE") = TRUNC(SYSDATE) THEN TO_CHAR
        ("M"."DATE", 'HH24:MI') ELSE TO_CHAR(TRUNC("M"."DATE")) END) AS"date"
      FROM portal_ticket t,
           portal_contact c,
           (select tm.ticket_id,
                   m.*,
                   RANK() OVER(PARTITION BY tm.ticket_id ORDER BY "M"."DATE" DESC, ID) ordem
              FROM portal_ticket_message tm, portal_message m
             WHERE tm.message_id = m.id) m
    WHERE t.customer_identity = c.identity
       AND t.id = m.ticket_id
       AND m.ordem = 1
    ORDER BY t.sequential_id DESC

I have the "M"." DATE" which is of the DATE type (although the output is in CHAR, as can be seen in the query), but it has UTC data and I would like to convert them to our time zone, as I can do?

I’m having difficulty given the query is already complicated for me (where I used CASE for, if the record is from today, return only the hours, IF NO, return only the date).

Answer 1

First, program the data when performing operations that are date and time with time. Separate in column, after that perform the calculations. Try creating a view with the information in the format you want and then using your already organized data. Remembering the dd/MM/yyyy form (date)