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!
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.
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
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;
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.
- Video by Connor McDonald: The UTC timezone. Why SYSDATE might be wrong for you
- Documentation: SQL Translation Framework