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
