I think that column is newer than 12.1 :) Looks like 18cR1 it was added.
Thanks,
On Fri, Sep 23, 2022 at 10:38 AM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Hi Chris,
You’re missing the begin_interval_time_tz column. This has a time zone and
can therefore be converted between time zones.
Dates and timestamps (without time zone) need to be implicitly converted
to timestamp with time zone data type for `at time zone`. Presumably, this
uses your session time zone rather than the database time zone. But as
usual, you shouldn’t rely on implicit behaviour. Use the from_tz function
to treat a date/timestamp without a time zone as a timestamp with time
zone.
Guessing, the dba_hist_snapshot view probably is doing functions against
the _tz column to expose the two columns you’re using.
Hope that helps,
Andy
On Fri, Sep 23, 2022 at 6:33 AM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
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
----------------------------------------
----------------------------------------
+00:00 US/Eastern
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
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
