[sql] Different CURRENT_TIMESTAMP and SYSDATE in oracle

After executing this SQL in oracle 10g:

SELECT SYSDATE, CURRENT_TIMESTAMP  FROM DUAL

I receive this strange output:

What is cause of the difference in time? The server time is equal of SYSDATE value

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.

Note: SYSDATE - returns only date, i.e., "yyyy-mm-dd" is not correct. SYSDATE returns the system date of the database server including hours, minutes, and seconds. For example:

SELECT SYSDATE FROM DUAL; will return output similar to the following: 12/15/2017 12:42:39 PM

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.

1. SYSDATE, systimestamp return datetime of server where database is installed. SYSDATE - returns only date, i.e., "yyyy-mm-dd". systimestamp returns date with time and zone, i.e., "yyyy-mm-dd hh:mm:ss:ms timezone"
2. now() returns datetime at the time statement execution, i.e., "yyyy-mm-dd hh:mm:ss"
3. CURRENT_DATE - "yyyy-mm-dd", CURRENT_TIME - "hh:mm:ss", CURRENT_TIMESTAMP - "yyyy-mm-dd hh:mm:ss timezone". These are related to a record insertion time.

• SYSDATE provides date and time of a server.
• CURRENT_DATE provides date and time of client.(i.e., your system)
• CURRENT_TIMESTAMP provides data and timestamp of a clinet.