Hello all,
I understand that there is tons of info available on this topic but I couldn't get an answer specific to my question. I might have a wrong understanding on this topic, and hence my question.
--using Oracle 18c
SELECT SYSTIMESTAMP FROM DUAL;
--18-MAY-20 04.07.45.330583000 PM -04:00
SELECT DBTIMEZONE FROM DUAL;
--+00:00
SELECT SESSIONTIMEZONE FROM DUAL;
--America/New_York
DROP TABLE TEST_T;
CREATE TABLE TEST_T
(RECORD_ID NUMBER, DATE_CREATED TIMESTAMP WITH LOCAL TIME ZONE);
ALTER SESSION SET TIME_ZONE = '-07:00';
SELECT SESSIONTIMEZONE FROM DUAL;
-- -07:00
INSERT INTO TEST_T VALUES (1, TIMESTAMP '2009-09-03 00:00:00.000000 PST');
SELECT * FROM TEST_T;
--1 03-SEP-09 03.00.00.000000000 AM --why it it giving me 3am.. shouldn't it return midnight because my session timezone is same as what I have in insert statement
ALTER SESSION SET TIME_ZONE = '-04:00';
SELECT SESSIONTIMEZONE FROM DUAL;
-- -04:00
SELECT * FROM TEST_T;
--03-SEP-09 03.00.00.000000000 AM -- alter session timezone made no difference.. why?
Thanks,
RN