This is a recurring topic but I still could not find a solution. As I understand SYSTIMESTAMP will return the time based on server timezone and CURRENT_TIMESTAMP will return it based on client timezone.
I am connecting to an remote Oracle database from my house. If I run:
SELECT
DBTIMEZONE, SESSIONTIMEZONE
FROM
dual;
I get:
DBTIMEZONE SESSIONTIMEZONE
----------------- -----------------
America/Sao_Paulo America/Sao_Paulo
But if I try:
select systimestamp, current_timestamp from dual;
I get:
SYSTIMESTAMP CURRENT_TIMESTAMP
---------------------------------- ---------------------------------------------
26/01/21 15:52:46,259759000 -03:00 26/01/21 16:52:46,259770000 AMERICA/SAO_PAULO
I double checked tzdata info on server side and can't figure out what I am missing. Can anyone help?
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).