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.
Oracle Autonomous Database provides the init parameter SYSDATE_AT_DBTIMEZONE that enables special handling in a session for the date and time value returned in calls to SYSDATE and SYSTIMESTAMP.
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;
The Other Way Around
In case you want to sync the session timezone with the database timezone, i.e., CURRENT_DATE and CURRENT_TIMESTAMP to return the database timezone values, then you need to start the client with the environment variable ORA_SDTZ set to ‘DB_TZ’ or set the session parameter time_zone to ‘dbtimezone’:
SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE
------------------------- -------------------------
03-07-2023 11:42:36 03-07-2023 12:42:36
-- export ORA_SDTZ=DB_TZ
-- sqlplus ...
-- or
SQL> alter session set time_zone=dbtimezone;
Session altered.
SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE
------------------------- -------------------------
03-07-2023 11:43:59 03-07-2023 11:43:59
Many thanks to my dear colleague Robert Pastijn for highlighting this topic.
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
- Video by Connor McDonald: The UTC timezone. Why SYSDATE might be wrong for you
- Documentation: SQL Translation Framework
Database Heartbeat 