Re: Timestamp Oddity in 12.1 with DBA_HIST_SNAPSHOT

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