Sysdate Vs Current date in Autonomous database

 
SYSDATE is sql function that returns the current date and time for the database server Operating system, where as CURRRENT_DATE returns the current date and time in the session time zone, the same applies to SYSTIMESTAMP and CURRENT_TIMESTAMP also.
 
When both database and application server are set in the same Time zone, both functions return the same value. Now what happens if we migrate database to a cloud platform (server in a different timezone), say Autonomous database that goes with default timezone as UTC ?
 
Now, if application is using SYSDATE or SYSTIMESTAMP, but expect to get a local time, then we are 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
 
However Oracle Autonomous database, provides a workaround, to sync the session timezone with the database time zone, that is CURRENT_DATE and CURRENT_TIMESTAMP return the database time zone values by setting the session parameter time_zone to dbtimezone.
 
 
demo@ATP19C> select sysdate, current_date from dual;
 
SYSDATE                 CURRENT_DATE
----------------------- -----------------------
27-JUL-2023 03:43:41 pm 27-JUL-2023 09:13:41 pm
 
demo@ATP19C> alter session set time_zone = dbtimezone;
 
Session altered.
 
demo@ATP19C> select sysdate, current_date from dual;
 
SYSDATE                 CURRENT_DATE
----------------------- -----------------------
27-JUL-2023 03:43:51 pm 27-JUL-2023 03:43:51 pm
 
demo@ATP19C>