Different CURRENT_TIMESTAMP and SYSDATE in oracle
SYSDATE
, SYSTIMESTAMP
returns the Database's date and timestamp, whereas current_date
, current_timestamp
returns the date and timestamp of the location from where you work.
For eg. working from India, I access a database located in Paris. at 4:00PM IST:
select sysdate,systimestamp from dual;
This returns me the date and Time of Paris:
RESULT
12-MAY-14 12-MAY-14 12.30.03.283502000 PM +02:00
select current_date,current_timestamp from dual;
This returns me the date and Time of India:
RESULT
12-MAY-14 12-MAY-14 04.00.03.283520000 PM ASIA/CALCUTTA
Please note the 3:30 time difference.
SYSDATE returns the system date, of the system on which the database resides
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE
execute this comman
ALTER SESSION SET TIME_ZONE = '+3:0';
and it will provide you the same result.
CURRENT_DATE
and CURRENT_TIMESTAMP
return the current date and time in the session time zone.
SYSDATE
and SYSTIMESTAMP
return the system date and time - that is, of the system on which the database resides.
If your client session isn't in the same timezone as the server the database is on (or says it isn't anyway, via your NLS settings), mixing the SYS*
and CURRENT_*
functions will return different values. They are all correct, they just represent different things. It looks like your server is (or thinks it is) in a +4:00 timezone, while your client session is in a +4:30 timezone.
You might also see small differences in the time if the clocks aren't synchronised, which doesn't seem to be an issue here.