RE: Strange (?) behaviour with SYSTIMESTAMP
- From: "Dunbar, Norman (Capgemini)" <norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <stbaldwin@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Oct 2010 11:51:01 +0100
Hi Steve,
>> As you can see, setting my session time zone makes no
>> difference to the value returned by SYSTIMESTAMP
SYSDATE and SYSTIMESTAMP are defined to return the DATE/Time from the
server that the database resides on and not the time on the client
desktop or similar. Hence, regardless of how you set the SESSION time
zone, SYSTIMESTAMP will always give you back the server time complete
with time zone.
>> but makes a
>> considerable difference to any arithmetic performed using
>> SYSTIMESTAMP.
SYSDATE has no time zone information. SYSTIMESTAMP does - you are not
really calculating with similar data types, so one or other will get
converted. In your case, SYSDATE is cast to a TIMESTAMP WITH TIME ZONE
to match the SYSTIMESTAMP return value.
You can prove this as the results from these two queries is identical:
select systimestamp - (cast (sysdate as timestamp with time zone)) from
dual;
select systimestamp - sysdate from dual;
The interesting results you see when performing calculations are down to
the fact that SYSTIMESTAMP is giving you the server time in the servers
time zone while, unfortunately, SYSDATE is being cast into your SESSION
time zone, as follows:
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
------------------------------------------------------------------------
---
+01:00
SQL> select systimestamp as TS,
2 cast (sysdate as timestamp with time zone) as DT from dual;
TS DT
-------------------------------- --------------------------------
27-OCT-10 11.37.04.709956 +01:00 27-OCT-10 11.37.04.000000 +01:00
So far so good. Now if you change your session time zone and rerun the
above, the following results:
SQL> alter session set time_zone = '+05:00';
Session altered.
SQL> select systimestamp as TS,
2 cast (sysdate as timestamp with time zone) as DT from dual;
TS DT
-------------------------------- --------------------------------
27-OCT-10 11.39.27.471268 +01:00 27-OCT-10 11.39.27.000000 +05:00
Major problem, and possibly a bug - I just can't quite work it out yet!
:-(
If you try casting both to TIMESTAMP WITH LOCAL TIME ZONE a similar
problem occurs:
SQL> select cast (systimestamp as timestamp with local time zone) as TS,
2 cast (sysdate as timestamp with local time zone) as DT
3 from dual;
TS DT
------------------------- -------------------------
27-OCT-10 15.41.48.709157 27-OCT-10 11.41.48.000000
But the same problem exists. just the other way around. SYSTIMESTAMP is
cast into the server time plus your session time zone adjustment - which
actually makes sense given that that is exactly what we requested, while
SYSDATE remains at the current (server) date in the servers Time zone.
I would say that this sort of date arithmetic is probably best avoided!
Sorry to introduce more problems than solutions!
Cheers,
Norm.
Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should
still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under
the Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may
also be accessed by someone other than the sender or recipient, for business
purposes.
If we have sent you information and you wish to use it please read our terms
and conditions which you can get by calling us on 08708 506 506. Find out more
about the Environment Agency at www.environment-agency.gov.uk
--
//www.freelists.org/webpage/oracle-l
Other related posts:
