Awesome DATE / TIMESTAMP enhancement in Autonomous Database

Date management is a commonly encountered issue when customers want to move their database to a cloud environment. If you are using a dedicated VM, then of course you can set the timezone to whatever is appropriate to your region. However, a dedicated VM in a cloud environment is hardly getting the benefit of “cloud”, because you’re just spending the same amount of time and resources looking after a server like you always did.

But if you migrate to a managed database service, most likely that database is going to be running as UTC timezone. That seems a sensible setting until you realise two things:

1) SYSDATE and SYSTIMESTAMP always come back as UTC no matter what the database timezone is set to. For example, my autonomous database is set to Perth (+08:00) but everything still comes back as UTC. The date functions that do respect the database timezone are CURRENT_DATE and CURRENT_TIMESTAMP

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sysdate from dual;

SYSDATE
-------------------
01/02/2022 23:32:28

SQL>
SQL> select systimestamp from dual;

SYSTIMESTAMP
-----------------------------------------------
01-FEB-22 11.32.28.816637 PM +00:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
02/02/2022 07:39:21

SQL>
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------
02-FEB-22 07.39.21.626182 AM +08:00

2) That you coded all of your applications using SYSDATE and SYSTIMESTAMP when you should have been using CURRENT_DATE and CURRENT_TIMESTAMP 😢

And lets face it…no-one want to be changing every date reference in their code to deal with this when you migrate to the cloud.

I did video a while back covering a mechanism of avoiding that effort by taking advantage of the SQL Translation facilities in Oracle to automatically convert “SYSDATE” references to “CURRENT_DATE” as the code is passed through to the database. This could serve as a stopgap solution until the code could be refactored.

However, there is now an easier solution on our autonomous databases. There’s a new parameter SYSDATE_AT_DBTIMEZONE which will do the heavy lifting for you. Let’s look at what happens to our familiar SYSDATE and SYSTIMESTAMP functions

SQL> alter session set sysdate_at_dbtimezone=true;

Session altered.

SQL>
SQL> select systimestamp from dual;

SYSTIMESTAMP
------------------------------------------------------------
02-FEB-22 07.32.30.147772 AM +08:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
02/02/2022 07:32:30

Now its so much easier to migrate your “non-UTC” databases to Autonomous!

Documentation here