Different CURRENT_TIMESTAMP and SYSDATE in oracle

SqlOraclePlsqlOracle10g

Sql Problem Overview


After executing this SQL in oracle 10g:

SELECT SYSDATE, CURRENT_TIMESTAMP  FROM DUAL

I receive this strange output: Toad output for query

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

Sql Solutions


Solution 1 - Sql

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.

Solution 2 - Sql

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.

Solution 3 - Sql

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.

Solution 4 - Sql

  • 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.

Solution 5 - Sql

Note: SYSDATE - returns only the 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

Solution 6 - Sql

  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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMohsen KashiView Question on Stackoverflow
Solution 1 - SqlAlex PooleView Answer on Stackoverflow
Solution 2 - SqlRaMs_YearnsToLearnView Answer on Stackoverflow
Solution 3 - SqlHarshitView Answer on Stackoverflow
Solution 4 - SqlreddyView Answer on Stackoverflow
Solution 5 - SqlAmir AbeView Answer on Stackoverflow
Solution 6 - Sqlsohan kumawatView Answer on Stackoverflow