Different CURRENT_TIMESTAMP and SYSDATE in oracle

oracle plsql oracle10g
Mohsen Kashi picture Mohsen Kashi · Jul 29, 2013 · Viewed 162.1k times · Source

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

Answer

Alex Poole picture Alex Poole · Jul 29, 2013

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.

Related questions

How to execute an oracle stored procedure?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it …

Read More
How to use BOOLEAN type in SELECT statement

I have a PL/SQL function with BOOLEAN in parameter: function get_something(name in varchar2, ignore_notfound in boolean); This function is a part of 3rd party tool, I cannot change this. I would like to use this function …

Read More
check if "it's a number" function in Oracle

I'm trying to check if a value from a column in an oracle (10g) query is a number in order to compare it. Something like: select case when ( is_number(myTable.id) and (myTable.id >0) ) then 'Is a number …

Read More