Different CURRENT TIMESTAMP and SYSDATE in oracle

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  now   returns datetime at the time statement execution  i e    yyyy-mm-dd hh mm ss  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

User · Answer

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

User · Answer

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

[sql] Different CURRENT_TIMESTAMP and SYSDATE in oracle

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g