Convert UTC? timezone to EST in Oracle on select

Question

Our company's Oracle server is hosted in the east coast of the US and I believe follows a default timezone of EST. I have a stored procedure which logs messages but puts the current timestamp into a field declared as timestamp.

describe log_messages;
Name      Null?    Type           
--------- -------- -------------- 
ENTRY_ID  NOT NULL NUMBER         
SEVERITY           VARCHAR2(1)    
DATE_TIME          TIMESTAMP(6)   
MESSAGE            VARCHAR2(2048)

Usually, I do something like

insert into log_messages(severity,date_time,message) 
values('I',current_timestamp,'some message');

If I do select * from log_messages, the time stamps look like this:

28-MAY-20 01.50.15.747963000 AM

However, the above time is actually 4 hours later than my current timezone.

select entry_id,severity,cast(date_time as timestamp with time zone) as date_time, date_time AT TIME ZONE 'EST' AS est,message
from log_messages 
order by date_time desc;

In the above, it thinks the timestamp in the timestamp field is in EST time, and then when it converts the other, it subtracts 4 hours from it. Effectively, it is as though it writes the time in UTC, but then reads it in thinking it is EST, and subtracts 4 hours from it. How can I get the real time in my local EST time?

Have you tried using DBtimezone for the schema ? SELECT DBTIMEZONE FROM DUAL; — Avi, May 31 '20 at 03:42
See this Post : **strong text** https://stackoverflow.com/a/35759699/3024770 — Hossein NooriAzad, May 31 '20 at 06:16
@Avi, DBTIMEZONE has **nothing(!)** to do with this question, see [How to handle Day Light Saving in Oracle database](https://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926) — Wernfried Domscheit, May 31 '20 at 08:50

score 0 · Answer 1 · answered May 31 '20 at 08:49

CURRENT_TIMESTAMP returns the current date and time in your session time zone, i.e. location or time zone settings of your Oracle Database server is not relevant.

You can interrogate your session time zone with select SESSIONTIMEZONE from dual; and change it with ALTER SESSION SET TIME_ZONE=...

However, as the data type is TIMESTAMP(6), the time zone information is lost as soon as the data is inserted.

When you run cast(date_time as timestamp with time zone) then Oracle takes your SESSIONTIMEZONE for conversion, actually Oracle runs cast(FROM_TZ(date_time, SESSIONTIMEZONE) as timestamp with time zone)

Unless you don't alter your session time zone, the two queries should return the same value..

See also How to handle Day Light Saving in Oracle database

Convert UTC? timezone to EST in Oracle on select

1 Answers1