This post is part of a series of blog posts on the Oracle Autonomous Database.
Please be careful when using the SYSDATE function in Oracle Autonomous Database!
SYSDATE is used extensively in PL/SQL based applications. What will happen when you migrate your data to Autonomous Cloud (or build a new application on Autonomous) as by default the Time Zone is set to Coordinated Universal Time (UTC) in an OCI environment?
You are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular all SQL using SYSDATE and SYSTIMESTAMP might not return what you expect. For example, you might need to check what happened during the previous hour (sysdate-1/24) – and you might be surprised – you will get the result for the past one hour but it might not be your last hour.
Currently, it is not possible to change the timezone of SYSDATE and SYSTIMESTAMP since it is coming from the operating system. The SYSDATE and SYSTIMESTAMP functions simply perform a system-call to the server Operating System to get the time – the “gettimeofday” call. The server OS (Unix) timezone settings influences the time that the OS will pass on to Oracle and returned by SYSDATE and SYSTIMESTAMP.
So, how can we get around this issue?
The general recommendation is to use CURRENT_DATE and CURRENT_TIMESTAMP which will return the date in session timezone.
In a globally distributed database environment, UTC is the recommended time zone to use.
– How to change the Time Zone in Oracle Database hosted in OCI with an Example: Doc ID 2459830.1
– Timestamps and time zones – Frequently Asked Questions: Doc ID 340512.1
– How to Change the “Database Time” (SYSDATE and SYSTIMESTAMP) to another Time/Timezone: Doc ID 1988586.1