current_timestamp and sysdate are diffrent.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: current_timestamp and sysdate are diffrent.

Hybrid View

  1. #1
    Join Date
    May 2012
    Posts
    3

    current_timestamp and sysdate are diffrent.

    Hi,

    I am facing a problem of having difference in sysdate and current_timestamp. Server date matches with sysdate but not with current_timestamp.

    $ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Wed May 30 16:29:04 2012

    Copyright (c) 1982, 2009, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options

    SQL> SELECT CURRENT_TIMESTAMP,to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Now" from dual;

    CURRENT_TIMESTAMP
    ---------------------------------------------------------------------------
    Now
    ------------------------
    21-JUN-12 05.29.22.503772 PM +00:00
    Wed 30-May-2012 16:29:22


    SQL> !date
    Wed May 30 16:29:26 IST 2012


    ...

    Please Help
    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    SYSDATE returns the date and time that belongs to the host where the database is sitting as reported by the Operating System.

    On the other hand, CURRENT_TIMESTAMP depends on the session.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2012
    Posts
    3
    PAVB,

    Thanks for reply. But this fact is known to me. I have already check the OS date and not changed current timestamp. Still facing same problem.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Are you in the same timezone as the database server? Because it could be that CURRENT_TIMESTAMP is specific to the time on the client machine, rather than the server. Just a thought...
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by maheop View Post
    Thanks for reply. But this fact is known to me. I have already check the OS date and not changed current timestamp. Still facing same problem.
    It is the other way around, SYSDATE depends on the server while CURRENT_TIMESTAMP depends on the session a.k.a. the client session.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Oct 2008
    Posts
    15
    difference of few hours can be understood...but you are having 21 days diff..there is definetely somthing wrong...

    SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

    can you paste the output?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width