Timestamp Oddity in 12.1 with DBA_HIST_SNAPSHOT
Date: Fri, 23 Sep 2022 09:33:18 -0400
Message-ID: <CAP79kiQON_adh+zd0uhjBiew6VK770G+Xe8-ycpZm5pRxPTNNA_at_mail.gmail.com>
So this is a weird one to me.
The database I'm working in runs on a server in UTC time and dbtimezone is set to "+00:00". So all normal there *for now.*
Now this is where things get weird with DBA_HIST_SNAPSHOT.
I'm in Eastern Time Zone (AMERICA/NEW_YORK). I set my Windows Time Zone to UTC and I login to the database and check session time zone:
select dbtimezone, sessiontimezone from dual /
DBTIMEZONE SESSIONTIMEZONE ------------------------------ ------------------------------
+00:00 UTC
Now let's query BEGIN_SNAP_TIME from DBA_HIST_SNAPSHOT and convert it to US/Eastern time at the same time:
select snap_id,
begin_interval_time,
begin_interval_time at time zone 'US/Eastern' as bet_eastern
from dba_hist_snapshot
where begin_interval_time >= sysdate -7/24 order by snap_id desc
/
SNAP_ID BEGIN_INTERVAL_TIME BET_EASTERN
---------- ----------------------------------------
------------------------------------
68622 23-SEP-22 01.00.18.292 PM 23-SEP-22 09.00.18.292
AM US/EASTERN
68621 23-SEP-22 12.00.11.664 PM 23-SEP-22 08.00.11.664
AM US/EASTERN
68620 23-SEP-22 11.00.05.731 AM 23-SEP-22 07.00.05.731
AM US/EASTERN
68619 23-SEP-22 10.00.57.695 AM 23-SEP-22 06.00.57.695
AM US/EASTERN
68618 23-SEP-22 09.00.50.232 AM 23-SEP-22 05.00.50.232
AM US/EASTERN
68617 23-SEP-22 08.00.41.100 AM 23-SEP-22 04.00.41.100
AM US/EASTERN
All good, right?
Now, if I set my Windows Time Zone to US/Eastern (instead of UTC) and re-run that query, this happens:
SQL> select dbtimezone, sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE ---------------------------------------- ----------------------------------------from dba_hist_snapshot
+00:00 US/Eastern
select snap_id, begin_interval_time, begin_interval_time at time zone 'US/Eastern' as bet_eastern
where begin_interval_time >= sysdate -7/24 order by snap_id desc
/
SNAP_ID BEGIN_INTERVAL_TIME BET_EASTERN
---------- ----------------------------------------
-----------------------------------
68622 23-SEP-22 01.00.18.292 PM 23-SEP-22 01.00.18.292
PM US/EASTERN
68621 23-SEP-22 12.00.11.664 PM 23-SEP-22 12.00.11.664
PM US/EASTERN
68620 23-SEP-22 11.00.05.731 AM 23-SEP-22 11.00.05.731
AM US/EASTERN
68619 23-SEP-22 10.00.57.695 AM 23-SEP-22 10.00.57.695
AM US/EASTERN
68618 23-SEP-22 09.00.50.232 AM 23-SEP-22 09.00.50.232
AM US/EASTERN
68617 23-SEP-22 08.00.41.100 AM 23-SEP-22 08.00.41.100
AM US/EASTERN
So because my sessiontimezone is Eastern, and trying to select it at
Eastern time, the database doesn't do any conversion. (Which *kind of *makes
sense if Oracle considers the raw data timezone-less and my session is
already set to US/Eastern)
What doesn't make sense is that the BEGIN_INTERVAL_TIME by default isn't applying my session timezone offset and instead continues to display in UTC even when session timezone = US/Eastern.
So I'm assuming here that the data must be timezone-less (timezone not stored).
I see that DBA_HIST_SNAPSHOT has a separate SNAP_TIMEZONE column that shows +00 01:00:00.000000.
Currently the only solution to this that I can think of is setting my local variable to UTC and then always manually converting columns with an "AT TIME ZONE 'US/Eastern'" clause (which means rewriting a lot of scripts if I want them to show my time).
Is there any other way to get Oracle to automatically apply the offset to data thats stored in timestamp columns via session parameter or anything?
Chris
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 23 2022 - 15:33:18 CEST