Different Current_Timestamp and Sysdate in Oracle

Different CURRENT_TIMESTAMP and SYSDATE in oracle

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.

Strange behavior of systimestamp and sysdate

When you do:

if systimestamp between systimestamp and systimestamp then

the non-deterministic systimestamp call is just being evaluated three times, and getting very slightly different results each time.

You can see the same effect with

if systimestamp >= systimestamp then

which also always returns false.

Except, it's not quite always. If the server is fast enough and/or the platform it's on has low-enough precision for timestamp fractional seconds (i.e. on Windows, which I believe still limits the precision to milliseconds) then all of those calls could still get the same value some or most of the time.

Things are a bit different in SQL; as an equivalent:

select *
from dual
where systimestamp between systimestamp and systimestamp;

will always return a row, so the condition is always true. That is explicitly mentioned in the documentation:

All of the datetime functions that return current system datetime information, such as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated once for each SQL statement, regardless how many times they are referenced in that statement.

There is no such restriction/optimisation (depending on how you look at it) in PL/SQL. When you use between it does say:

The value of the expression x BETWEEN a AND b is defined to be the same as the value of the expression (x>=a) AND (x<=b) . The expression x will only be evaluated once.

and the SQL reference also mentions that:

In SQL, it is possible that expr1 will be evaluated more than once. If the BETWEEN expression appears in PL/SQL, expr1 is guaranteed to be evaluated only once.

but it says nothing about skipping evaluation of a and b (or expr2 or expr3) even for system datetime functions in PL/SQL.

So all three expressions in your between will be evaluated, it will make three separate calls to systimestamp, and they will all (usually) get slightly different results. You effectively end up with:

if initial_time between initial_time + 1 microsecond and initial_time + 2 microseconds then

or to put it another way

if (initial_time >= initial_time + 1 microsecond) and (initial_time <= initial_time + 2 microseconds) then

While (initial_time <= initial_time + 2 microseconds) is always going to be true, (initial_time >= initial_time + 1 microsecond) has to be false - unless the interval between the first and third evaluations is actually zero for that platform/server/invocation. When it is zero the condition evaluates to true; the rest of the time, when there is any measurable delay, it will evaluate to false.

---

Your other examples all manipulate the timestamp in way that removes the fractional seconds, by turning some or all of the results into dates, as @Connor showed (and I alluded to in comments). Those aren't really relevant to your core question of why if systimestamp between systimestamp and systimestamp then is (usually) false.

Same timezone but current_timestamp differs from systimestamp

It would be so much nicer if the Oracle documentation would be clearer on such things, wouldn't it?

sys.... (like sysdate and systimestamp) are the date-time/timestamp of the operating system running the database software (the "server"). There is no Oracle function to retrieve the operating system time zone; however, you can see it reported when you call systimestamp.

On the other hand, dbtimezone is a pretty much arbitrary time zone, set when the db is created; often it is set to UTC (GMT). It is not used for anything except for values of data type timestamp with LOCAL time zone - such values are converted to the "database" time zone and stored on disk as timestamps without a time zone component, in that time zone.

The dbtimezone only needs to be consistent with itself - it has absolutely nothing to do with the time zone of the operating system on the server. You can change the DB time zone at will - except when the database has tables with columns of type timestamp with LOCAL time zone and not all those columns are empty.

Which means that dbtimezone is pretty meaningless; it doesn't really control anything. It has nothing to do with systimestamp, and there is no function to simply display to you the server's operating system time zone.

So, back to what I said at the top... it would be so much nicer if the documentation... right?

You might want to check the current timestamp on your server (if you have access to it), and/or its time zone directly. You may find, for example, that it is set to a fixed offset from UTC, and therefore it may not reflect daylight saving time. Or whatever. But that is the correct "thing" that you should look at, to compare to current_timestamp and sessiontimezone. How you do that on the server (even assuming you have access to it) will depend on the operating system - Windows, Linux, Unix, etc. In any case, your systimestamp shows '-03:00` as if it were a fixed offset from UTC.

EDIT - Looking into it a bit further, if I understand correctly, Brazil has chosen on an exceptional(??) basis not to observe daylight saving time this year. So, it seems that -03:00 offset is correct. Based on the values you reported, it seems that Oracle doesn't know that, and it is treating "America/Sao_Paulo" as being affected by DST. That is why the systimestamp is correct, while current_timestamp is off by one hour.

Either Oracle doesn't "know" about this, or it does and it released a patch that has not been applied to your system. Or whatever. Clearly the operating system on the server DOES know about this. The issue is in the database, and it's not about "db" or "session" time zone (since they are the same), but the time zone itself, and what it means in this exceptional year.

For what it's worth, I just tested on my system - if I select systimestamp at time zone '-03:00' I get the correct current time in Sao Paulo (checked online); if I change the time zone to 'America/Sao_Paulo' I get an hour later. This is to be expected - my system is not patched (I am not a paying customer, I just play with a "practice" version of the database, which doesn't qualify me for patches).

Difference between NOW(), SYSDATE() & CURRENT_DATE() in MySQL

Current_date() will only give you the date.

now() give you the datetime when the statement,procedure etc... started.

sysdate() give you the current datetime.

Look at the seconds after waiting 5 seconds between now()1 sysdate()1 with the following query (scroll to the right):

select now(),sysdate(),current_date(),sleep(5),now(),sysdate();

-- will give
-- now()    sysdate()   current_date()  sleep(5)    now()1  sysdate()1
-- 6/10/2014 2:50:04 AM 6/10/2014 2:50:04 AM    6/10/2014 12:00:00 AM   0   6/10/2014 2:50:04 AM 6/10/2014 2:50:09 AM

Force Oracle's sysdate to return different value for multiple statements

I found that current_timestamp does the job:

drop table t;
create table t(a timestamp);
insert into t values (current_timestamp);
insert into t values (current_timestamp);
insert into t values (current_timestamp);
select * from t;

Outputs:

A                          
---------------------------
18/12/25 04:48:54,134000000
18/12/25 04:48:54,142000000
18/12/25 04:48:54,149000000

Different time sysdate and current_date when connect to database since application server

The solution was posted on DBA stackexchange: https://dba.stackexchange.com/a/314026/175060.

You have to set the parameter at cluster level:

srvctl setenv database -db DBNAME -env "TZ=Atlantic/Canary".

And then restart the DB hosts, since a CRS restart is not enough.

Difference between SYSDATE(),NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP() in MySQL?

They aren't all synonymous.

SYSDATE() is different than NOW(), in a significant way. As a demonstration:

SELECT NOW(), SYSDATE(), SLEEP(5), NOW(), SYSDATE()

The output might look like this:

(playing where's waldo to find the difference, its the return from the last expression that is different, a second invocation of SYSDATE, after a delay of 5 seconds, returns a value that differs by 5 seconds)

2018-03-01 11:09:19
2018-03-01 11:09:19
0
2018-03-01 11:09:19
2018-03-01 11:09:24

(There are some scenarios where we want to use SYSDATE() rather than NOW())

MySQL provides a variety of expressions that can return the same result

DATE(NOW()) vs. CURRDATE()

I suspect that this is large part due to a desire to make transition from other Relational DBMS easier, by more closely matching (where possible and appropriate) the function names and syntax used by Oracle, SQL Server, and so on.

---

Related Topics