Ora

How do I change the time zone in Oracle Apex?

Published in Oracle APEX Time Zones 8 mins read

Managing time zones effectively in Oracle APEX is vital for applications serving a global user base. APEX allows for flexible time zone control at multiple levels, ensuring that date and time information is displayed accurately and relevantly to each user, while maintaining data integrity in the database.

Here’s a comprehensive guide on how to change and manage time zones within your Oracle APEX environment.

Understanding Time Zone Management in APEX

Time zones in Oracle APEX can be configured and influenced at various points, from the underlying database to specific application and user settings. Each level plays a role in how dates and times are stored, processed, and ultimately displayed.

Let's explore each method for adjusting time zones:

1. Instance-Wide or Workspace-Default Time Zone

For administrators, establishing a default time zone for the entire APEX instance or the underlying Oracle Cloud service is a foundational step. This global setting ensures a consistent baseline for applications and users that do not specify their own preferences.

  • How to Change:

    • As an administrator, access the administrative console of your Oracle Cloud environment (if APEX is hosted there, e.g., on an Autonomous Database) or the APEX instance administration area.
    • Within the administration navigation menu, look for Settings.
    • Navigate to a General page or a similar section.
    • Here, you'll typically find options for Time Zone and Language, allowing you to select a default time zone, language, and date/time format. This choice acts as the system-wide default for new applications or users unless overridden at a more specific level.

  • Impact: Sets the default time zone for the entire APEX environment, affecting how time is interpreted when more specific settings are not present.

2. Database Time Zone

The database's time zone (DBTIMEZONE) dictates how TIMESTAMP WITH LOCAL TIME ZONE data is stored internally and how functions like SYSDATE and SYSTIMESTAMP behave without explicit session time zone settings.

  • How to View:

    SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;

  • How to Change:

    • During Database Creation: The DBTIMEZONE is typically set when the database is created.
    • Post-Creation (DBA Action): Changing DBTIMEZONE on an existing database requires specific DBA privileges and often involves database downtime. It is done using the ALTER DATABASE SET TIME_ZONE = 'UTC' or ALTER DATABASE SET TIME_ZONE = '-05:00' command, followed by a database restart.
    • For Autonomous Database, this might be managed through the cloud console rather than direct SQL.

  • Impact: Crucial for the internal storage and conversion of TIMESTAMP WITH LOCAL TIME ZONE data. SESSIONTIMEZONE (which defaults to DBTIMEZONE if not otherwise set) affects how SYSTIMESTAMP behaves.

3. APEX Application Level Time Zone

You can set a specific time zone for an individual APEX application, overriding the instance-wide default. This is useful for applications that operate primarily within a particular geographic region or for consistency across all users of that application.

  • How to Change:

    1. Log in to your APEX workspace.
    2. Navigate to the desired application in App Builder.
    3. Click on Shared Components.
    4. Under "Globalization," click Application Definition Attributes.
    5. Locate the Application Primary Time Zone attribute.
    6. Select the desired time zone from the list (e.g., America/New_York, Europe/London).
    7. Click Apply Changes.

  • Impact: This setting acts as the default time zone for all sessions within that specific application, unless overridden by user or session-level settings. It influences how APEX built-in date/time functions or items without explicit formatting handle time zones.

4. User-Specific Time Zone Preferences

For truly global applications, allowing individual users to set their preferred time zone is the most user-friendly approach. This ensures dates and times are always displayed in their local context.

  • How to Implement:

    1. Create a User Profile Page: Develop an APEX page where users can select their preferred time zone (e.g., from a select list populated by valid time zone regions from V$TIMEZONE_NAMES).
    2. Store Preference: Store this preference in a user settings table (e.g., APP_USERS.PREFERRED_TIME_ZONE).
    3. Apply Session Time Zone: Upon user login or application load, retrieve the user's preferred time zone and use a Session-Level Time Zone mechanism to apply it. A common approach is an Application Computation (for APEX_UTIL.SET_SESSION_TIME_ZONE) or a Login Processing step.

  • Impact: Provides a personalized experience, displaying dates and times relative to each user's location.

5. Session-Level Time Zone

The session time zone is dynamic and can be set for the duration of a user's session. This is particularly powerful when implementing user-specific time zones or for specific processes that require a temporary time zone change.

  • How to Change:

    • Using APEX_UTIL.SET_SESSION_TIME_ZONE (Recommended for APEX):
      • This built-in APEX utility sets the session time zone. You can call it in an Application Process (e.g., "On New Instance" or "Before Header"), a Login Process, or a page process.
      • Example: APEX_UTIL.SET_SESSION_TIME_ZONE('America/Los_Angeles');
    • Using ALTER SESSION (SQL/PL/SQL):
      • Execute ALTER SESSION SET TIME_ZONE = 'America/New_York'; or ALTER SESSION SET TIME_ZONE = '-05:00';
      • This command is often used within specific PL/SQL packages or procedures.

  • Impact: Overrides database and application default time zones for the current user session. Affects how SYSTIMESTAMP, CURRENT_TIMESTAMP, and TIMESTAMP WITH LOCAL TIME ZONE values are interpreted and displayed within that session.

6. Displaying Dates and Times (SQL and PL/SQL)

Regardless of the underlying time zones, you often need to explicitly format dates and times for display to ensure clarity and user-friendliness.

  • How to Format:

    • TO_CHAR with Time Zone Elements: Use TO_CHAR with format models like TZH (Time Zone Hour), TZM (Time Zone Minute), TZR (Time Zone Region), and TZD (Time Zone Abbreviation).

      -- Display a timestamp with its original time zone
SELECT TO_CHAR(your_timestamp_column, 'MM/DD/YYYY HH24:MI:SS TZR') FROM your_table;

-- Convert to a specific time zone for display
SELECT TO_CHAR(your_timestamp_column AT TIME ZONE 'America/New_York', 'MM/DD/YYYY HH24:MI:SS') FROM your_table;

    • AT TIME ZONE Clause: This clause allows you to convert a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE to a different time zone.

      SELECT SYSTIMESTAMP AT TIME ZONE 'Europe/London' FROM DUAL;

    • APEX Item/Column Formatting: In APEX Builder, for report columns or page items of type Date/Time, you can specify a "Format Mask" (e.g., DD-MON-YYYY HH24:MI TZR).

  • Impact: Controls the visual representation of dates and times without altering the stored data. Essential for clarity and user understanding.

Time Zone Control Levels Summary

Level of Control Description How to Change/Manage Primary Impact
Instance/Workspace Default Baseline time zone set by an administrator for the entire APEX environment or underlying cloud service. Admin Settings: Navigate to the administration area (e.g., Oracle Cloud console, APEX instance admin), find 'Settings' -> 'General' -> 'Time Zone and Language' to select defaults. Provides a system-wide default for new applications and sessions.
Database (DBTIMEZONE) The inherent time zone of the Oracle Database itself. DBA Action: Set during database creation or modify via ALTER DATABASE SET TIME_ZONE = '...' (requires restart). Influences DBTIMEZONE and how TIMESTAMP WITH LOCAL TIME ZONE is stored.
APEX Application A specific time zone defined for an individual APEX application. Shared Components: In APEX Builder, navigate to Shared Components -> Application Definition Attributes -> Globalization. Set the "Application Primary Time Zone" attribute. Overrides instance defaults for all users within that specific application.
User-Specific Time zone preference set by an individual application user. User Profile Page: Implement a form where users select their preferred time zone. Programmatic: Use APEX_UTIL.SET_SESSION_TIME_ZONE based on stored user preference during login. Personalizes the application experience for each user.
Session-Level Time zone active for the current user session. PL/SQL: Call APEX_UTIL.SET_SESSION_TIME_ZONE('America/New_York') at the beginning of a session (e.g., in a Login process). SQL: ALTER SESSION SET TIME_ZONE = 'timezone_string'; Affects how SYSTIMESTAMP, CURRENT_TIMESTAMP, and TIMESTAMP WITH LOCAL TIME ZONE are interpreted within the session.
Display/Report Level How dates and times are formatted for visual presentation in reports, forms, or pages. SQL/PL/SQL: Use TO_CHAR(your_date, 'MM/DD/YYYY HH24:MI:SS TZR') or TO_CHAR(date AT TIME ZONE 'Region', '...'). APEX Item/Column: Apply format masks in APEX Builder for items and report columns. Controls the visual representation without changing underlying data.

Practical Insights and Best Practices

  • Store TIMESTAMP WITH LOCAL TIME ZONE: For columns storing date and time, TIMESTAMP WITH LOCAL TIME ZONE is often the most flexible data type. It stores the time in the database's DBTIMEZONE but automatically converts it to the SESSIONTIMEZONE when retrieved. This simplifies development as you don't need to manually convert stored times for display.
  • Prioritize User Preferences: For global applications, always aim to implement user-specific time zone settings. This provides the best user experience.
  • Use APEX_UTIL.GET_SESSION_TIME_ZONE: To know the currently active session time zone within your APEX application, use APEX_UTIL.GET_SESSION_TIME_ZONE.
  • Consistent Formatting: Always explicitly format dates and times for display using TO_CHAR or APEX format masks to avoid ambiguity and ensure a consistent user interface.
  • Testing: Thoroughly test your application with users from different time zones to ensure all date and time displays and calculations are correct.

By leveraging these various levels of control, you can precisely manage how time zones are handled in your Oracle APEX applications, providing accurate and localized experiences for all users.

← Previous Article
What is Closed Punctuation in a Business Letter?
Next Article →
What is a Double Tap Pistol?