Dontcheff

SYSDATE and Time Zones in the Autonomous Database

In Autonomous, Cloud, DBA, Oracle database on May 4, 2020 at 06:16

In the Oracle database, SYSDATE is one of the most popular functions, arguably the most popular.

PL/SQL based applications use quite extensively the SYSDATE function. What will happen when you migrate their data to Autonomous Cloud (or build a new application on Autonomous) as by default the Time Zone is set to Coordinated Universal Time (= UTC) in an OCI environment?

You are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular all SQL using SYSDATE and SYSTIMESTAMP might not return what you expect. You need to check for instance what happened during the last one hour (sysdate-1/24) – and you might be surprised – you will get the result for the past one hour but it might not be your last hour.

Currently, it is not possible to change the timezone of SYSDATE and SYSTIMESTAMP since they are coming from the operating system. The SYSDATE and SYSTIMESTAMP functions simply performs a system-call to the server Operating System to get the time – the so called “gettimeofday” call. The server OS (Unix) timezone settings influences the time that the OS will pass on to Oracle and returned by SYSDATE and SYSTIMESTAMP.

Moreover, there is no Oracle database parameter/init.ora setting that will impact the SYSDATE and SYSTIMESTAMP timezone (= give a result that is in an other timezone than the OS timezone setting). Also, SYSDATE and SYSTIMESTAMP do not use the Oracle timezone information (= Oracle RDBMS dst patches) in the database and are NOT affected by the used DBTIMEZONE / SESSIONTIMEZONE settings.

On a side note, in the Oracle database there is a dynamic init.ora parameter called FIXED_DATE which enables you to set a constant date that SYSDATE will always return instead of the current date.

As far as I know, there is a project going on inside Oracle to provide a parameter to make SYSDATE return date in a database timezone, but there is no ETA for it.

So, how can we bypass this issue?

The general recommendation is to use CURRENT_DATE and CURRENT_TIMESTAMP which will return the date in session timezone. If you cannot modify the applications, then there is a workaround to use the SQL Translation Framework functionality (Hello Kerry!) to change SYSDATE to CURRENT_DATE in the queries on the fly. This would require recreating the PL/SQL procedures, so that they can be replaced as well. It also requires creating a schema level logon rigger to enable the Translation Framework. This needs to be carefully tested, but in general it works.

UTC is the recommended time zone to use, and of course, in theory at least, this makes a lot of sense in a global distributed database environment. In reality, everyone like his/her own time zone!

These 4 links below are worth reading if you are interested in timestamp and time zone issues and functionalities:

How to change the Time Zone in Oracle Database hosted in OCI with an Example: Doc ID 2459830.1
Timestamps and time zones – Frequently Asked Questions: Doc ID 340512.1
How to Change the “Database Time” (SYSDATE and SYSTIMESTAMP) to another Time/Timezone: Doc ID 1988586.1
Datetime Data Types and Time Zone Support

The following are some points to note related to time zone upgrade in a multitenant environment:

– Updating the time zone version of a CDB does not change the time zone version of the PDBs
– Updating the time zone version of a PDB does not change the time zone version of the other PDBs
– A new PDB is always assigned the time zone version of PDB$SEED
– PDB$SEED is always assigned the time zone version at the time of CDB creation
– The time zone version of PDB$SEED cannot be changed

And if you plan on running ALTER DATABASE SET TIME_ZONE, have in mind that:

– The ALTER DATABASE SET TIME_ZONE statement returns an error if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data
– The change does not take effect until the database has been shut down and restarted
– You can find out the database time zone by entering the following query: SELECT dbtimezone FROM DUAL;

SET TIME_ZONE can be done also on session level if needed:

alter session set time_zone='-03:00';

And please note once more that SYSDATE and SYSTIMESTAMP have nothing to with the DATABASE TIMEZONE (DBTIMEZONE).

On a final note: the information above is valid for all flavors of Autonomous: ADW, ATP, ADW-D and ATP-D.

===

Years ago, I saw this quiz on dbasupport.com – we have the following two PL/SQL blocks:

 

BEGIN
  WHILE sysdate = sysdate LOOP
    NULL;
  END LOOP;
END;
/
 
DECLARE
  x DATE;
BEGIN
  LOOP
    BEGIN
      SELECT null INTO x FROM dual WHERE sysdate = sysdate;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;
    END;
  END LOOP;
END;
/

Question is: what happens after you run them? Are the loops above both finite, both infinite or is it so that one of them is finite and the other one infinite?

If you cannot answer the question just run them in SQL*Plus, etc. Then, try to explain why – the reason for being finite or infinite.

  1. Hi Julian, great post thanks. If you have a large DB in Europe/London time migrating to OCI ATP that contains a variety of audit columns, audit tables, data history tables and transaction history tables and all using DATE not Timestamp data-types. My current thinking is to migrate the DB as is and replace any sysdate calls with a custom sysdate function along the lines of “cast(cast(sysdate as timestamp) at time zone ‘Europe/London’ as date)”. I feel this is the lowest risk and least effort. Any thoughts?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: