Tip Tuesday | Set Dates and Times in Autonomous DB and APEX

Lucas Hirschegger -

As you might know, Autonomous Databases are set to the UTC time zone by default. While you can alter this setting to match your preferred time zone, it may not always be convenient to do so.

Instead, you can use the following tips to properly handle times both in the database and in your APEX applications.

  • Consider using TIMESTAMP WITH TIME ZONE rather than DATE as a data type in your tables. This ensures the exact date and time of the record is preserved. This is mandatory if your application operates in more than one time zone.
  • Use CURRENT_TIMESTAMP or SYSTIMESTAMP depending on your use case. CURRENT_TIMESTAMP returns the session time, while SYSTIMESTAMP returns the database time.
  • Use APEX substitution strings rather than hardcoded format masks. This is imperative in globalised applications. APP_NLS_DATE_FORMAT and APP_DATE_TIME_FORMAT will help you achieve this and can be adjusted to your specific needs under Globalization Attributes.
  • In APEX, use the substitution string SYSDATE_YYYYMMDD for today's date to avoid repeatedly querying the database. Additionally, use the lesser-known SINCE substitution string to calculate the time elapsed from a given datetime.
  • In APEX, you can easily convert dates to local time by enabling Automatic Time Zone under Globalization Attributes. Query the TIMESTAMP WITH TIME ZONE column using the AT TIME ZONE clause, retrieving the session time zone from the SESSIONTIMEZONE function.
select ID,
       EVENT_TITLE,
       EVENT_TIME,
       EVENT_TIME at time zone SESSIONTIMEZONE EVENT_TIME_LOCAL
  from TST_EVENTS

By following these tips, you can effectively manage time zones within your Autonomous Database and APEX applications. This ensures accurate data storage and presentation regardless of user location, leading to a more robust and user-friendly experience.