Re: Timestamp Oddity in 12.1 with DBA_HIST_SNAPSHOT

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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l