Will you live with over-simplified :) . You can also modify the
s_crsconfig_<nodename>_env.txt file in RAC, set environment variables in
listener startup scripts, in the really old days have the listener on a
different machine! and so on. I don't think I've ever come across a site
that actually did this, and I imagine that any site that did do this would
be conscious of what they were doing, which is why I didn't add yet more
hypotheticals..Never the less Stefan's answer is better than mine.
In nearly all cases, i.e where neither Oracle nor the O/S has been
reconfigured post-install, then sysdate/systimestamp will be in the same
timezone as the db server. Current_XXXX will be that time translated to the
client session timezone (and preserve timezone information). LOCALTIMESTAMP
which I missed out returns the data in the session timezone, but in a
timestamp datatype, i.e minus timezone.
Finally I definitely agree with Stefan's recommendation not to use
SYSDATE|TIMESTAMP if you explicitly need to rely upon a given timezone.
Easier said than changed mind.
On Thu, Apr 30, 2015 at 5:39 AM, Stefan Knecht <knecht.stefan@xxxxxxxxx>
wrote:
That is not actually correct. The docs are also somewhat lacking precision
on this one. SYSDATE and SYSTIMESTAMP do NOT return the time / timezone of
the server or host; instead, they return the time of the process that
handed over the connection to the database.
Take this simple scenario:
Database instance FOO on a box that runs with server time set to EST.
You run sqlplus locally, and SYSDATE will return the current time in EST.
What you would expect.
BUT, if you connect to the very same instance through the listener via
TNS, SYSDATE will return whatever timezone was set when the listener was
started. That can be different to the host time zone.
I think the docs should be amended to indicate this.
You could do:
export TZ=Europe/Warsaw
Start a listener
export TZ=Asia/Bangkok
Start a second listener
And you can end up with three different results returned by
SYSDATE/SYSTIMESTAMP based on how you connect to the same instance.
I'd recommend to always use SYSTIMESTAMP AT TIME ZONE ... if you depend on
your code returning the time in a specific time zone. Don't rely on the
"host" time since that can vary.
Stefan
On Apr 29, 2015 11:12 PM, "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
wrote:
That's correct. The CURRENT_XXXX functions return the server time(stamp)
in the session timezone of the client. The SYS functions return the server
time(stamp) in the database server timezone. They are all documented in the
Database SQL Reference. 12c is at
http://docs.oracle.com/database/121/SQLRF/toc.htm with for example
current_timestamp at
http://docs.oracle.com/database/121/SQLRF/functions052.htm.
On Wed, Apr 29, 2015 at 8:39 AM, Jose Soares <jose.soares@xxxxxxxxxxxxxx>
wrote:
I suspect that current_date is not equal to sysdate and
current_timestamp is not equal to systimestamp.
Could someone explain me the difference between them?
Take a look at these examples:
The first timestamp has tz but the last one...
sql=> select current_timestamp,current_date,current_timestamp from dual
current_timestamp | current_date | current_timestamp
--------------------------+ -------------------+ -------------------
2015-04-29 09:19:03.252091| 2015-04-29 09:19:03| 2015-04-29 09:19:03
(1 rows)
let me use sysdate instead of current_date...uhm! there's a difference...
sql=> select current_timestamp,sysdate,current_timestamp from dual
current_timestamp | sysdate | current_timestamp
--------------------------+ -------------------+
--------------------------
2015-04-29 09:26:19.498600| 2015-04-29 09:26:19| 2015-04-29
09:26:19.498600
sql=> select systimestamp,sysdate,systimestamp from dual
systimestamp | sysdate | systimestamp
--------------------------+ -------------------+
--------------------------
2015-04-29 09:31:47.995794| 2015-04-29 09:31:47| 2015-04-29
09:31:47.995794
(1 rows)
in this case, current_date and current_timestamp are equal ...
sql=> select current_date,current_timestamp from dual
current_date | current_timestamp
-------------------+ -------------------
2015-04-29 09:17:46| 2015-04-29 09:17:46
(1 rows)
Why in this case current_date and current_timestamp are not equal? ...
sql=> select current_timestamp,current_date from dual
current_timestamp | current_date
--------------------------+ -------------------
2015-04-29 09:17:58.694202| 2015-04-29 09:17:58
(1 rows)
it says current_date length is 10 but I see 19 chars...
it says current_timestamp length is 26 but I see 19 chars...
sql=> select
current_date,length(current_date),current_timestamp,length(current_timestamp)
from dual
current_date | length(current_date)| current_timestamp |
length(current_timestamp)
-------------------+ --------------------+ -------------------+
-------------------------
2015-04-29 09:16:15| 10 | 2015-04-29 09:16:15| 26
(1 rows)
j
--
//www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
