Re: Timestamp Oddity in 12.1 with DBA_HIST_SNAPSHOT

Ah, if you’re not planning on upgrading any time soon, then you’ll need to use from_tz.

Annoyingly it looks like the timezone reported in the table doesn’t fit the expected inputs, you can try using the dbtimezone function but check that it’s what you expect it to be (UTC), you can also just hardcode 'UTC'.

Once you’ve from_tz’ed the timestamp to give it the right time zone, you can convert it to your desired time zone with `at time zone`

Thanks,
Andy

On Fri, Sep 23, 2022 at 7:57 AM, Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> 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