SYSDATE vs. CURRENT_DATE in Oracle Database

Introduction

SYSDATE is a SQL function that returns the current date and time set for the operating system of the database server. CURRENT_DATE returns the current date in the session time zone. The same story applies to SYSTIMESTAMP and CURRENT_TIMESTAMP.

When database and application servers are set to the same time zone, both functions will return the same value, so it doesn’t matter what function is used in the application’s SQL code. In the following example, both SQL client and database server are in the Europe/Berlin time zone, which is two hours ahead of UTC in the summer. Even though I can’t really call it summer when it comes to Germany.

alter session set NLS_DATE_FORMAT='MM-DD-YYYY HH24:MI:SS';
set lines 300
col sysdate for a25
col current_date for a25
col systimestamp for a40
col current_timestamp for a40

SQL> select sysdate, current_date from dual;
SQL> select systimestamp, current_timestamp from dual;

So both values show 11:42 AM.

Now, what happens when we migrate the database to a server in a different time zone, e.g. UTC?

SYSDATE and SYSTIMESTAMP show 9:42 instead, as the operating system where the database runs is configured to UTC.

Now, if your application is using SYSDATE or SYSTIMESTAMP, but expects to get a local time, then you’re in trouble!

Recommended Solution

If you are expecting to get the database client’s local time, always use CURRENT_DATE and CURRENT_TIMESTAMP. This makes your application independent of in what time zone the database server runs.

However, for existing applications, this means changing the application code is required.

Workarounds?

Change the Database Timezone

This wouldn’t help!!!

Database Timezone and Session Timezone are different concepts. Setting Database Timezone only changes how time information is stored for some datatypes like TIMESTAMP WITH LOCAL TIMEZONE.

SQL> select dbtimezone from dual;

   DBTIMEZONE
_____________
+00:00

SQL> alter database  set time_zone = 'Europe/Berlin';

Database altered.

-- restart the database

SQL> select dbtimezone from dual;

      DBTIMEZONE
________________
Europe/Berlin

SQL> select sysdate, current_date from dual;
SQL> select systimestamp, current_timestamp from dual;

SYSDATE and SYSTIMESTAMP still return UTC time, 9:45 meanwhile.

Using SQL Translation Framework

SQL WHAT?

This was also my first reaction when I came across this while working on a PoC for a customer in Germany.

SQL Translation Framework was introduced to translate the SQL statements of a client program from a foreign (non-Oracle) SQL dialect into the SQL dialect used by the Oracle Database SQL compiler.

And now guess what are we going to do!!!

Exactly, translate each occurrence of SYSDATE to CURRENT_DATE and each occurrence of SYSTIMESTAMP to CURRENT_TIMESTAMP on the fly when the SQL statements are executed, without changing the application code.

Create the following in your database:

-- create the translator
SQL> create or replace package date_translator is
  procedure translate_sql(
                 sql_text in clob, 
                 translated_text out clob );
  procedure translate_error(error_code in binary_integer, translated_code out binary_integer, translated_sqlstate out varchar2);
end;
/

Package DATE_TRANSLATOR compiled

SQL> create or replace package body date_translator is
  procedure translate_sql(
    sql_text in clob, 
    translated_text out clob ) is
  begin
    translated_text := 
         regexp_replace(sql_text, 'SYSDATE', 'CURRENT_DATE', 1, 0, 'i');
    translated_text := 
         regexp_replace(translated_text, 'SYSTIMESTAMP', 'CURRENT_TIMESTAMP', 1, 0, 'i');
  end;
  
  procedure translate_error(error_code in binary_integer, translated_code out binary_integer, translated_sqlstate out varchar2) is
  begin
    null;
  end;
end;
/

Package Body DATE_TRANSLATOR compiled

-- create the translation profile
SQL> begin
  dbms_sql_translator.create_profile(
     profile_name => 'DATE_TRANSLATION_PROFILE');
   
 dbms_sql_translator.set_attribute(
    profile_name   => 'DATE_TRANSLATION_PROFILE',
    attribute_name => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
    attribute_value=> dbms_sql_translator.ATTR_VALUE_FALSE);

  dbms_sql_translator.set_attribute(
     profile_name   => 'DATE_TRANSLATION_PROFILE',
     attribute_name => dbms_sql_translator.attr_translator,
     attribute_value=> '<yourschema>.DATE_TRANSLATOR');

end;
/

PL/SQL procedure successfully completed.

-- grant the necessary privileges
SQL> grant all on sql translation profile DATE_TRANSLATION_PROFILE to <yourschema>;

Grant succeeded.

Now, enable the profile at the session level and execute your SQL statements:

SQL> alter session set sql_translation_profile = DATE_TRANSLATION_PROFILE;

Session altered.

SQL> select sysdate, current_date from dual;
SQL> select systimestamp, current_timestamp from dual;

SYSDATE and SYSTIMESTAMP both return the Europe/Berlin time now.

Did you recognize the column header???

To switch back to “normal”, set the profile to NULL.

SQL> alter session set sql_translation_profile = null;

Session altered.

SQL> select sysdate, current_date from dual;
SQL> select systimestamp, current_timestamp from dual;

Conclusion

SYSDATE and SYSTIMESTAMP SQL functions return the date and time of the operating system of the database server. If your application expects to get the client’s local time, use CURRENT_DATE and CURRENT_TIMESTAMP instead.

The recommended solution is to use CURRENT_DATE and CURRENT_TIMESTAMP. If you can’t change your application code, then SQL Translation Framework might be a possible workaround.

The SQL Translation Framework was not invented to be used for that. At the end of the day, it’s just a possible workaround. So it is highly recommended to do complete testing of application and code to make sure everything works as expected. 

Further Reading

Would you like to get notified when the next post is published?